﻿using AutoMapper;
using Dapper;
using DocumentFormat.OpenXml.Bibliography;
using DocumentFormat.OpenXml.Drawing;
using DocumentFormat.OpenXml.Drawing.Charts;
using DocumentFormat.OpenXml.Math;
using DocumentFormat.OpenXml.Office2013.Drawing.ChartStyle;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Wordprocessing;
using FFMpegCore.Enums;
using housingsafety.server.Caching.Core;
using housingsafety.server.Entities;
using housingsafety.server.Entities.DataModels;
using housingsafety.server.Helpers;
using housingsafety.server.Models;
using housingsafety.server.Repository;
using housingsafety.server.Security;
using housingsafety.server.Svcs;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using Minio;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Npgsql;
using NPOI.POIFS.Crypt.Dsig;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using SixLabors.ImageSharp.ColorSpaces;
using StackExchange.Redis;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Core.Common.CommandTrees.ExpressionBuilder;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.Json;
using static housingsafety.server.Controllers.VillageMngController;

namespace housingsafety.server.Controllers
{
    /// <summary>
    /// 房屋管理服务
    /// </summary>
    public class HouseMngController : BaseController<HouseMngController>
    {
        public IConfiguration Configuration { get; }
        private readonly IRepository<ZmVillageFireSafety> _villageFireSafetyRepository;
        private readonly ICache _cache;
        private readonly IMapper _mapper;
        private readonly IStorageProvider _storageProvider;
        private readonly ZmDbContext _dbContext;
        private readonly IRepository<ZmUser> _userRepository;
        private readonly IRepository<ZmBuildings> _buildingsRepository;
        private readonly IRepository<ZmBuildingsLayer> _buildingsLayerRepository;
        private readonly IRepository<ZmUpdateLog> _zmupdateRepository;
        private readonly IRepository<ZmUpdateLog> _zmupdate;
        private readonly IRepository<ZmFileAttach> _fileAttachRepository;
        private readonly IRepository<ZmPlaceCommercial> _placeCommercialRepository;
        private readonly IRepository<ZmPlaceResident> _placeResidentRepository;
        private readonly IOptions<StaticDataConf> _staticVtOptions;
        private readonly IRepository<ZmDepartment> _departmentRepository;
        private readonly IRepository<RiskCensusHouseYhpc> _riskHouseRepository;
        private readonly IRepository<RiskCensusYhpcUpldFile> _riskHouseFileRepository;
        private readonly IRepository<ZmPlaceResidentCheck> _placeResidentCheckRepository;
        private readonly IRepository<ZmPlaceCommercialCheck> _placeCommercialCheckRepository;
        private readonly IRepository<ZmYtPhoto> _zmYtPhotoRepository;
        private readonly IDbTransaction _transaction;

        private readonly Microsoft.AspNetCore.Http.Features.FormOptions _defaultFormOptions = new Microsoft.AspNetCore.Http.Features.FormOptions();
        private readonly string[] _permittedExtensions = { ".mp3", ".mp4", ".jpg", ".jpeg", ".png", ".geojson", ".doc", ".docx" };

        private static object _lock = new object();

        public HouseMngController(IStorageProvider storageProvider
            , IRepository<ZmDepartment> departmentRepository
            , IRepository<ZmUser> userRepository
              , IRepository<ZmVillageFireSafety> villageFireSafetyRepository
              , IRepository<ZmBuildings> buildingsRepository
            , IRepository<ZmBuildingsLayer> buildingsLayerRepository
            , IRepository<ZmFileAttach> fileAttachRepository
            , IRepository<ZmPlaceCommercial> placeCommercialRepository
            , IRepository<ZmPlaceResident> placeResidentRepository
            , IRepository<RiskCensusHouseYhpc> riskHouseRepository
            , IRepository<RiskCensusYhpcUpldFile> riskHouseFileRepository
            , IRepository<ZmPlaceResidentCheck> placeResidentCheckRepository
            , IRepository<ZmPlaceCommercialCheck> placeCommercialCheckRepository
            , IRepository<ZmYtPhoto> zmYtPhotoRepository
            , IMapper mapper, ICacheProvider cacheProvider, ZmDbContext dbContext
            , IOptions<StaticDataConf> staticVtOptions
            , ILogger<HouseMngController> logger,
            IConfiguration configuration
            , IRepository<ZmSystemLog> systemLogRepository, IRepository<ZmUpdateLog> zmupdateRepository) : base(logger, systemLogRepository)
        {
            _zmupdateRepository = zmupdateRepository;
            _storageProvider = storageProvider;
            _mapper = mapper;
            _dbContext = dbContext;
            _cache = cacheProvider.CreateCache();
            _zmYtPhotoRepository = zmYtPhotoRepository;
            _userRepository = userRepository;
            _buildingsRepository = buildingsRepository;
            _buildingsLayerRepository = buildingsLayerRepository;
            _fileAttachRepository = fileAttachRepository;
            _placeCommercialRepository = placeCommercialRepository;
            _placeResidentRepository = placeResidentRepository;
            _staticVtOptions = staticVtOptions;
            _departmentRepository = departmentRepository;
            _riskHouseRepository = riskHouseRepository;
            _riskHouseFileRepository = riskHouseFileRepository;
            _placeResidentCheckRepository = placeResidentCheckRepository;
            _placeCommercialCheckRepository = placeCommercialCheckRepository;
            Configuration = configuration;
            _villageFireSafetyRepository = villageFireSafetyRepository;
            _zmupdateRepository = zmupdateRepository;

        }


        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("houses/search-attr/ExportExcel")]
        [Produces("application/octet-stream")]
        [ProducesResponseType(typeof(RespRet<BuildingPcYhLogDetailDto>), 200)]
        public IActionResult  MXExportExcel([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string txtKeyWord,  [FromQuery] string fwzt)
        {
            
            var userNumber = this.CurrentUser();
           
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string strWhere = "  ";
            if (user.RoleType == 1 || user.RoleType == 2)
            {
                strWhere += " and xzq!='东城区' and xzq!='西城区' ";
            }
            else if (user.RoleType == 3)  //区级
            {
                strWhere += " and xzqdm='" + model.RegionCode + "'";
            }
            else if (user.RoleType == 4)
            {
                string tdm = model.RegionCode;
                if (model.RegionCode.Length != 12)
                {
                    tdm = model.RegionCode + "000";
                }
                strWhere += " and towndm='" + tdm + "'";
            }
            else if (user.RoleType == 5)
            {
                strWhere += " and villagedm='" + model.RegionCode + "'";
            }

            if (!string.IsNullOrEmpty(txtKeyWord))
            {
                strWhere += " and (hzxm like '%" + txtKeyWord + "%' or fwbh like '%" + txtKeyWord + "%' or address like '%" + txtKeyWord + "%')";
            }


            if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && !string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and xzqdm='" + xzqdm + "' and towndm='" + towndm + "' and villagedm='" + villagedm + "'";
            }
            else if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and xzqdm='" + xzqdm + "' and towndm='" + towndm + "'";
            }
            else if (!string.IsNullOrEmpty(xzqdm) && string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and xzqdm='" + xzqdm + "'";
            }

         

            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";

            DateTime endData = Convert.ToDateTime(today);
            table_fwzt = "zm_building_fwzt_" + endData.Year;


            string sql = @"select ROW_NUMBER() OVER ()index,* from (SELECT xzq,town,village,fwbh,address,hzxm,'正常出租'  fwzt,xzqdm,towndm,villagedm FROM
	                   zm_buildings  ) t  where 1=1   and fwbh not in(select distinct fwbh from " + table_fwzt + "  where final_fwzt is not null and   final_create_on='" + today + " 23:59:59') "
                    + strWhere;

            var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            IWorkbook wb = new XSSFWorkbook();
            ISheet sheet = wb.CreateSheet(model.RegionName + "");

            IRow row = sheet.CreateRow(0);
            row.CreateCell(0).SetCellValue("序号");
            row.CreateCell(1).SetCellValue("区名称");
            row.CreateCell(2).SetCellValue("街/乡镇");
            row.CreateCell(3).SetCellValue("村/社区");
            row.CreateCell(4).SetCellValue("房屋编号");
            row.CreateCell(5).SetCellValue("地址");
            row.CreateCell(6).SetCellValue("房主姓名");
            row.CreateCell(7).SetCellValue("房屋状态");

            int i = 1;
            foreach (object obj in data)
            {
                Dictionary<string, object> dictionary = new Dictionary<string, object>();
                if (obj != null)
                {

                    var properties = obj as IDictionary<string, object>;
                    row = sheet.CreateRow(i);
                    foreach (var kvp in properties)
                    {
                        var val = kvp.Value + "";
                        if (kvp.Key == "index")
                        {
                            row.CreateCell(0).SetCellValue(i);
                        }
                        else if (kvp.Key == "xzq")
                        {
                            row.CreateCell(1).SetCellValue(kvp.Value + "");
                        }
                        else if (kvp.Key == "town")
                        {
                            row.CreateCell(2).SetCellValue(kvp.Value + "");
                        }
                        else if (kvp.Key == "village")
                        {
                            row.CreateCell(3).SetCellValue(kvp.Value + "");
                        }
                        else if (kvp.Key == "fwbh")
                        {
                            row.CreateCell(4).SetCellValue(kvp.Value + "");
                        }
                        else if (kvp.Key == "address")
                        {
                            row.CreateCell(5).SetCellValue(kvp.Value + "");
                        }
                        else if (kvp.Key == "hzxm")
                        {
                            row.CreateCell(6).SetCellValue(kvp.Value + "");
                        }
                        else if (kvp.Key == "fwzt")
                        {
                            row.CreateCell(7).SetCellValue(kvp.Value + "");
                        }
                    }
                    i++;
                }
            }

            byte[] buffer = null;
            using (MemoryStream ms = new MemoryStream())
            {
                wb.Write(ms);
                buffer = ms.ToArray();
            }
            return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "房屋明细表" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx");

           
        }


        /// <summary>
        /// 获取所有房屋列表（基础表 ----）
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("houses/search-attr")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<BuildingPcYhLogDetailDto>), 200)]
        public RespRet<Page<object>> SearchAttrList2([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] int pageIndex, [FromQuery] int limit, [FromQuery] string zc_yhzt, [FromQuery] string jc_yhzt, [FromQuery] string txtKeyWord, [FromQuery] string zczt, [FromQuery] string jczt, [FromQuery] string fwzt)
        {
            if (pageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }

            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string strWhere = "  ";
            if (user.RoleType == 1 || user.RoleType == 2 )
            {
                strWhere += " and xzq!='东城区' and xzq!='西城区' ";
            }
            else if (user.RoleType == 3)  //区级
            {
                strWhere += " and xzqdm='" + model.RegionCode + "'";
            }
            else if (user.RoleType == 4)
            {
                string tdm = model.RegionCode;
                if (model.RegionCode.Length != 12)
                {
                    tdm = model.RegionCode + "000";
                }
                strWhere += " and towndm='" + tdm + "'";
            }
            else if (user.RoleType == 5)
            {
                strWhere += " and villagedm='" + model.RegionCode + "'";
            }
           
            if (!string.IsNullOrEmpty(txtKeyWord))
            {
                strWhere += " and (hzxm like '%" + txtKeyWord + "%' or fwbh like '%" + txtKeyWord + "%' or address like '%" + txtKeyWord + "%')";
            }


            if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && !string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and xzqdm='" + xzqdm + "' and towndm='" + towndm + "' and villagedm='" + villagedm + "'";
            }
            else if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and xzqdm='" + xzqdm + "' and towndm='" + towndm + "'";
            }
            else if (!string.IsNullOrEmpty(xzqdm) && string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and xzqdm='" + xzqdm + "'";
            }

            //if (!string.IsNullOrEmpty(zczt))
            //{
            //    strWhere += " and zczt = '" + zczt + "' ";
            //}
            //if (!string.IsNullOrEmpty(jczt))
            //{
            //    strWhere += "and jczt = '" + jczt + "'";
            //}

            //if (!string.IsNullOrEmpty(zc_yhzt + ""))
            //{
            //    strWhere += " and zc_yhzt ='" + zc_yhzt + "' ";
            //}
            //if (!string.IsNullOrEmpty(jc_yhzt + ""))
            //{
            //    strWhere += " and jc_yhzt ='" + jc_yhzt + "' ";
            //}
            //if (!string.IsNullOrEmpty(fwzt + ""))
            //{
            //    strWhere += " and fwzt ='" + fwzt + "'";
            //}

            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
           
                DateTime endData = Convert.ToDateTime(today);
                table_fwzt = "zm_building_fwzt_" + endData.Year;


            string sql = @"select ROW_NUMBER() OVER ()index,* from (SELECT fwbh,xzqdm,xzq,town,towndm,village,villagedm,address,hzxm,'正常出租'  fwzt FROM
	                   zm_buildings  ) t  where 1=1   and fwbh not in(select distinct fwbh from " + table_fwzt + "  where final_fwzt is not null and   final_create_on='"+today+" 23:59:59') "
                    + strWhere+"    limit " +limit + " offset " + limit * pageIndex;

            string sql_count = @"select count(1)cnt from (SELECT fwbh,xzqdm,xzq,town,towndm,village,villagedm,address,hzxm FROM
	                   zm_buildings ) t  where 1=1 and fwbh not in(select distinct fwbh from " + table_fwzt + " where final_fwzt is not null and   final_create_on='" + today+" 23:59:59') " + strWhere;
          


            //执行语句
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
        
            var pageData = new Page<object>(list
                , pageIndex + 1, limit, count);
            return new RespRet<Page<object>>(true, "Ok", pageData);
        }


        /// <summary>
        /// 获取当前房屋的检查记录（包括自查、检查、核查）
        /// </summary>
        /// <param name="fwbh">房屋编号</param>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("houses/{fwbh}/baselog/list")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<Page<BuildingPcLogDto>>), 200)]
        public RespRet<Page<BuildingPcLogDto>> BaseLogList([FromRoute] string fwbh,
            [FromQuery] PagePcztRequestInput pageRequest)
        {
            if (pageRequest.PageIndex < 0)
            {
                return new RespRet<Page<BuildingPcLogDto>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<BuildingPcLogDto>>(false, "user not logined");
            }

            string strWhere = "";
            //隐患状态
            if (!string.IsNullOrEmpty(pageRequest.Pczt + ""))
            {
                strWhere += "and pczt =" + pageRequest.Pczt + "";
            }

            //隐患来源
            if (!string.IsNullOrEmpty(pageRequest.Yhly + ""))
            {
                strWhere += "and yhly = " + pageRequest.Yhly + "";
            }

            string sql = @"select  ROW_NUMBER() OVER ()index ,*   from (select b1.*,case when yhnum is null then '已自查无隐患' else  '已自查有隐患' end yhnum,
                         case when zgnum is null then '' else  '已整改' end zgnum  from (select t2.hzxm,t2.address,t1.fwbh,t1.pc_yh_id,t1.base_id,t1.id,t1.create_on 
                           from zm_pc_yhlog t1,(select *from (select t1.* ,t2.fwzt from zm_buildings t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh ) zm_buildings  where fwzt is   null) t2 where t1.fwbh=t2.fwbh ) b1 left join 
                           (select count(fwbh) yhnum, yhlog_id, sum(case when zg_value= '1' then 1 else 0 end) zgnum from zm_pc_yhlog_dx 
                           group by yhlog_id) b2 on b1.id = b2.yhlog_id)  t 
                       where fwbh='" + fwbh + "' " + strWhere + "  order by pcsj desc  limit " + pageRequest.Limit + " offset " + pageRequest.Limit * pageRequest.PageIndex;

            var list = _dbContext.Database.GetDbConnection().Query<BuildingPcLogDto>(sql).ToList();

            string sql_count = @"select  *   from (select b1.*,case when yhnum is null then '已自查无隐患' else  '已自查有隐患' end yhnum,
                         case when zgnum is null then '' else  '已整改' end zgnum  from (select t2.hzxm,t2.address,t1.fwbh,t1.pc_yh_id,t1.base_id,t1.id,t1.create_on 
                           from zm_pc_yhlog t1,(select *from (select t1.* ,t2.fwzt from zm_buildings t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh ) zm_buildings  where fwzt is   null) t2 where t1.fwbh=t2.fwbh ) b1 left join 
                           (select count(fwbh) yhnum, yhlog_id, sum(case when zg_value= '1' then 1 else 0 end) zgnum from zm_pc_yhlog_dx 
                           group by yhlog_id) b2 on b1.id = b2.yhlog_id)  t  where fwbh='" + fwbh + "'" + strWhere;
            var count = _dbContext.Database.GetDbConnection().Query<BuildingPcLogDto>(sql_count).ToList().Count;


            var pageData = new Page<BuildingPcLogDto>(list
                , pageRequest.PageIndex + 1, pageRequest.Limit, count);
            return new RespRet<Page<BuildingPcLogDto>>(true, "Ok", pageData);
        }

        




        /// <summary>
        /// 获取当前房屋的检查记录中的详细信息
        /// </summary>
        /// <param name="id">记录ID</param>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("houses/{id}/info/")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<BuildingGeomDto>), 200)]
        public RespRet<BuildingGeomDto> BaseLogInfo([FromRoute] string id)
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<BuildingGeomDto>(false, "user not logined");
            }



//            SELECT


//    zm_buildings.fwbh,
//	zm_buildings.hzxm,
//	zm_buildings.address,
//	zm_buildings.xzqdm as xzq,
//	zm_buildings.towndm as town,
//	zm_buildings.villagedm as village,
	
//CASE

//        WHEN(map_yh.pczt = '2') THEN

//        0 ELSE 1

//    END AS pczt2,
//	map_yh.jcyhcount,
//	map_yh.zcyhcount,
//    (zm_buildings.xzq || zm_buildings.town || zm_buildings.village) AS add2,
//     ST_X(ST_Centroid(zm_buildings.geom)) AS lng,
//    ST_Y(ST_Centroid(zm_buildings.geom)) AS lat
//FROM
//    zm_buildings

//    LEFT JOIN map_yh ON map_yh.fwbh = zm_buildings.fwbh



//    where zm_buildings.fwbh = '110114000910170'


            string sql = @" 	
SELECT
    zm_buildings.fwbh,
    zm_buildings.hzxm,
    zm_buildings.address,
    zm_buildings.xzqdm AS xzq,
    zm_buildings.towndm AS town,
    zm_buildings.villagedm AS village,
    (zm_buildings.xzq || zm_buildings.town || zm_buildings.village) AS add2,
    ST_X(ST_Centroid(zm_buildings.geom)) AS lng,
    ST_Y(ST_Centroid(zm_buildings.geom)) AS lat,
    ST_AsGeoJSON(zm_buildings.geom) AS geometry,
    ST_XMin(zm_buildings.geom) AS xmin,
    ST_XMax(zm_buildings.geom) AS xmax,
    ST_YMin(zm_buildings.geom) AS ymin,
    ST_YMax(zm_buildings.geom) AS ymax
FROM
    zm_buildings
	where zm_buildings.fwbh= '" + id + "'";
           var item = _dbContext.Database.GetDbConnection().Query<BuildingGeomDto>(sql).FirstOrDefault();

            if (item != null)
            {
                item.BoundingBox = new ZmRect
                {

                    XMin = item.XMin,
                    XMax = item.XMax,
                    YMin = item.YMin,
                    YMax = item.YMax,
                };
            }
            //var item = _dbContext.ZmBuildingsLayer.FromSqlRaw(sql).FirstOrDefault();
            //var dataDto = _mapper.Map<BuildingGeomDto>(item);
            return new RespRet<BuildingGeomDto>(true, "Ok", item);
        }


        private object JsonRegion(string jsonStr)
        {
            try
            {
                return JsonConvert.DeserializeObject<object>(jsonStr);
            }
            catch (System.Exception)
            {
            }

            return null;
        }



        /// <summary>
        /// 自查统计分析————根据行政区划统计基本信息情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("houses/basestatic/staticlist")]
        [ProducesResponseType(typeof(RespRet<Page<object>>), 200)]
        public RespRet<Page<object>> BaseLogList([FromQuery] PagePcztRequestInput pageRequest)
        {
            if (pageRequest.PageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            string strWhere = " ";
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;
            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


            if (model != null)
            {
                if (user.RoleType == 1 || user.RoleType == 2)
                {
                    strWhere += "and  xzq!='东城区' and xzq!='西城区'";
                }
                else if (user.RoleType == 3) //区级
                {
                    strWhere += " and xzqdm='" + model.RegionCode + "'";
                }
                else  //乡镇
                {
                    strWhere += "and xzqdm='" + model.RegionCode.Substring(0, 6) + "' ";
                }
            }
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string sql = "";
            if ( pageRequest.endtime == today)
            {
                sql = @"select ROW_NUMBER() OVER ()index ,* from jbxxgqtext_n_today('','','" + pageRequest.startime + " 00:00:00', ' "+ pageRequest.endtime + " 23:59:59', 'zm_pc_base') where 1=1 " + strWhere + " order by xzqdm ";

            }
            else 
            {
                string startimemonth = Convert.ToDateTime(pageRequest.startime).ToString("yyyy_MM");
                string endmonth = Convert.ToDateTime(pageRequest.endtime).ToString("yyyy_MM");
                string month = "";
                if (startimemonth == endmonth)
                {
                    month = startimemonth;
                    sql = @"select ROW_NUMBER() OVER ()index ,* from jbxxgqtext_n_today('ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,',' and row_num=1','" + pageRequest.startime + " 00:00:00','" + pageRequest.endtime + " 23:59:59', 'zm_pc_baselog_" + month + "') where 1=1 " + strWhere + " order by xzqdm ";

                }
                else 
                {
                    
                   sql = @"select ROW_NUMBER() OVER ()index ,* from jbxxgqtext_n('" + pageRequest.startime + " 00:00:00','" + pageRequest.endtime + " 23:59:59') where 1=1 " + strWhere + " order by xzqdm ";
                }
            }



            if (pageRequest.cked == "true") 
            {
                sql = @"select  ROW_NUMBER() OVER ()index ,* , (SELECT SUM(ccfhwggptzlsl)  FROM zm_pc_gjzz_cjlog d WHERE t.villagedm = d.villagedm
                ) AS ccfhwggptzlsl from (select t1.village, t1.townold town,t1.xzq,t2.towndm,t2.villagedm,t2.xzqdm, COALESCE(zcount,0)zcount, COALESCE(jzrks,0)jzrks,COALESCE(zzrs,0)zzrs,COALESCE(czryrs,0)czryrs,COALESCE(cjzwqtjyyt)cjzwqtjyyt,"
+ "COALESCE(fjsl,0)fjsl,COALESCE(jzfjs,0)jzfjs,COALESCE(jzscxyaqcksl,0)jzscxyaqcksl,COALESCE(jzxyssltsl,0)jzxyssltsl,COALESCE(ckfhwggptzlsl,0)ckfhwggptzlsl,COALESCE(bdfwjzrydddzxcsl,0)bdfwjzrydddzxcsl,"
+"COALESCE(mhqsl,0) mhqsl,COALESCE(fytsmz,0)fytsmz,COALESCE(xggdjsbp,0)xggdjsbp,COALESCE(xggdjsbpds,0)xggdjsbpds,COALESCE(no_xggdjsbpds,0)no_xggdjsbpds,COALESCE(jzsyjyplmhxts,0)jzsyjyplmhxts,"
+"COALESCE(jzszlwxdlgybjqs,0)jzszlwxdlgybjqs,COALESCE(wdwhjgfjgqsyyrkrgbdj,0)wdwhjgfjgqsyyrkrgbdj,COALESCE(sstdsyyrkrclzx,0)sstdsyyrkrclzx,COALESCE(gryssdcsszblyyqksddp,0)gryssdcsszblyyqksddp,"
+"  COALESCE(csjyssl,0)csjyssl,COALESCE(lcjzsl,0)lcjzsl,COALESCE(ssltdbfjsl,0)ssltdbfjsl,COALESCE(no_ssltdbfjsl,0)no_ssltdbfjsl,COALESCE(lc_dscount,0)lc_dscount,COALESCE(zzsggz,0)zzsggz,"
+"COALESCE(zzzdfa,0)zzzdfa,COALESCE(jzswhntjzssslt,0)jzswhntjzssslt,COALESCE(jzsnzssslt,0)jzsnzssslt,\"CY\",\"SD\",\"MRMF\",KTV,\"WB\",\"MJMJ\",\"QPS\",\"CS\",\"ZLAM\",\"JXPX\",\"YLZS\", \"JGSC\",\"qt\",lyxlhxjjzss from  " +
"jbxxcjtext_n_today('','','2024-01-01 00:00:00','"+DateTime.Now.ToString("yyyy-MM-dd")+" 23:59:59','zm_pc_base') t2 right join zdcmd t1  on t1.villagedm=t2.villagedm "
+ ") t where 1 = 1  order by villagedm";

            }

            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = list.Count;

            var pageData = new Page<object>(list
                , pageRequest.PageIndex + 1, pageRequest.Limit, count);
            return new RespRet<Page<object>>(true, "Ok", pageData);
        }
        /// <summary>
        /// 自查统计分析————根据乡镇统计基本信息
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("houses/basestatic/Townshipbaselist")]
        public RespRet<Page<object>> Townshipbaselist([FromQuery] string xzqdm,  [FromQuery] int pageindex, [FromQuery] int pagesize,[FromQuery] string startTime, [FromQuery] string endTime)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }


            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            string strWhere = " ";
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;
            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


            if (model != null)
            {
              
                if (user.RoleType == 1 || user.RoleType == 2)
                {
                  
                  strWhere += "and  xzq!='东城区' and xzq!='西城区'";
                }
                else if (user.RoleType == 3) //区级
                {
                    xzqdm = model.RegionCode;
                    strWhere += " and xzqdm='" + xzqdm + "'";
                }
                else  //乡镇
                {
                    string strtowndm = model.RegionCode;
                    if (model.RegionCode.Length != 12)
                    {
                        strtowndm += "000";
                    }

                    strWhere += "and xzqdm='" + xzqdm + "' and towndm='" + strtowndm + "'";
                }
            }
            if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime)) 
            {
                startTime = "2024-01-01";
                endTime = DateTime.Now.ToString("yyyy-MM-dd");
            }
            if (!string.IsNullOrEmpty(xzqdm))
            {
                strWhere += " and xzqdm='" + xzqdm + "'";
            }

            string sql = ""; string sql_count = "";
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string tablename = "";
            string filed = "";
            string fileWhere = "";
            if (endTime + "" == today)
            {
                tablename = "zm_pc_base";
                sql = @"select ROW_NUMBER() OVER ()index ,* from jbxxxztext_n_today('','','" + startTime + " 00:00:00', ' " + endTime + " 23:59:59', 'zm_pc_base') where 1=1 " + strWhere + " order by xzqdm ";
                sql_count = @"select ROW_NUMBER() OVER ()index ,* from jbxxxztext_n_today('','','" + startTime + " 00:00:00','" + endTime + " 23:59:59', 'zm_pc_base') where 1=1 " + strWhere + "  ";
            }
            else
            {
                string startimemonth = Convert.ToDateTime(startTime).ToString("yyyy_MM");
                string endmonth = Convert.ToDateTime(endTime).ToString("yyyy_MM");
                string month = "";
                if (startimemonth == endmonth)
                {
                    month = startimemonth;
                    tablename = "zm_pc_baselog_" + month;
                    filed = "ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,";
                    fileWhere = " and row_num=1 ";
                    sql = @"select ROW_NUMBER() OVER ()index ,* from jbxxxztext_n_today('" + filed + "','"+ fileWhere + "','" + startTime + " 00:00:00', ' " + endTime + " 23:59:59', '"+ tablename + "') where 1=1 " + strWhere + " order by xzqdm ";
                    sql_count = @"select ROW_NUMBER() OVER ()index ,* from jbxxxztext_n_today('" + filed + "','"+ fileWhere + "','" + startTime + " 00:00:00','" + endTime + " 23:59:59', '"+ tablename + "') where 1=1 " + strWhere + "  ";
                }
                else
                {
                    tablename = "zm_pc_baselog";
                    filed = "ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,";
                    fileWhere = " and row_num=1 ";
                    sql = @"select ROW_NUMBER() OVER ()index ,* from jbxxxztext_n('" + startTime + " 00:00:00','" + endTime + " 23:59:59') where 1=1 " + strWhere + "  limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + ""; ;
                    sql_count = @"select ROW_NUMBER() OVER ()index ,* from jbxxxztext_n('" + startTime + " 00:00:00','" + endTime + " 23:59:59') where 1=1 " + strWhere + "  ";
                }
            }
            
           
            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
           


            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });

        }
        /// <summary>
        /// 自查统计分析————根据村级统计基本信息
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("houses/basestatic/Villagebaselist")]
        public RespRet<Page<object>> Villagebaselist([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] int pageindex, [FromQuery] int pagesize, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string cked)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            string strWhere = "";
            if (!string.IsNullOrEmpty(xzqdm + ""))
            {
                strWhere += "and xzqdm ='" + xzqdm + "'";
            }
            if (!string.IsNullOrEmpty(towndm + ""))
            {
                strWhere += "and towndm ='" + towndm + "'";
            }
            if (string.IsNullOrEmpty(startTime))
            {
                startTime = "2024-01-01";
                endTime = DateTime.Now.ToString("yyyy-MM-dd");
            }
          
            if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm))
            {
                strWhere += " and xzqdm='" + xzqdm + "' and towndm='"+towndm+"'";
            }

            string sql = ""; string sql_count = "";
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string tablename = "";
            string filed = "";
            string fileWhere = "";
            if (endTime + "" == today)
            {
                tablename = "zm_pc_base";
                sql = @"select ROW_NUMBER() OVER ()index ,* from jbxxcjtext_n_today('','','" + startTime + " 00:00:00', ' " + endTime + " 23:59:59', 'zm_pc_base') where 1=1 " + strWhere + " order by xzqdm ";
                sql_count = @"select ROW_NUMBER() OVER ()index ,* from jbxxcjtext_n_today('','','" + startTime + " 00:00:00','" + endTime + " 23:59:59', 'zm_pc_base') where 1=1 " + strWhere + "  ";
            }
            else
            {
                string startimemonth = Convert.ToDateTime(startTime).ToString("yyyy_MM");
                string endmonth = Convert.ToDateTime(endTime).ToString("yyyy_MM");
                string month = "";
                if (startimemonth == endmonth)
                {
                    month = startimemonth;
                    tablename = "zm_pc_baselog_" + month;
                    filed = "ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,";
                    fileWhere = " and row_num=1 ";
                    sql = @"select ROW_NUMBER() OVER ()index ,* from jbxxcjtext_n_today('" + filed + "','" + fileWhere + "','" + startTime + " 00:00:00', ' " + endTime + " 23:59:59', '" + tablename + "') where 1=1 " + strWhere + " order by xzqdm ";
                    sql_count = @"select ROW_NUMBER() OVER ()index ,* from jbxxcjtext_n_today('" + filed + "','" + fileWhere + "','" + startTime + " 00:00:00','" + endTime + " 23:59:59', '" + tablename + "') where 1=1 " + strWhere + "  ";
                }
                else
                {
                    tablename = "zm_pc_baselog";
                    filed = "ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,";
                    fileWhere = " and row_num=1 ";
                    sql = @"select ROW_NUMBER() OVER ()index ,* from jbxxcjtext_n('" + startTime + " 00:00:00','" + endTime + " 23:59:59') where 1=1 " + strWhere + "  limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + ""; ;
                    sql_count = @"select ROW_NUMBER() OVER ()index ,* from jbxxcjtext_n('" + startTime + " 00:00:00','" + endTime + " 23:59:59') where 1=1 " + strWhere + "  ";
                }
            }
           
            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
          

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
              || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
              || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });

        }



        /// <summary>
        /// 房屋基本信息统计-根据不同的统计项获取房屋列表
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("houses/evolvestatic/getHourseByTypeList")]
        public RespRet<Page<object>> getHourseByTypeList([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string category, [FromQuery] int pageIndex, [FromQuery] int limit,  [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string txtKeyWord)
        {
            if (pageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }

            var sbSql = new StringBuilder();
            var sbSqlWhere = new StringBuilder();
            var sqlParams = new List<NpgsqlParameter>();

            var sbSqlBuildingEx = new StringBuilder();

            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);



            string strWhere = " and 1=1 ";
            string xzqWhere = "";
            string where = "";
            if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && !string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and xzqdm='" + xzqdm + "' and town='" + towndm + "' and village='" + villagedm + "'";
                xzqWhere = " and t1.xzqdm='" + xzqdm + "' and  t1.town='" + towndm + "' and  t1.village='" + villagedm + "'";
                where = " and xzqdm='" + xzqdm + "' and towndm='" + towndm + "' and villagedm='" + villagedm + "'";
            }
            else if (!string.IsNullOrEmpty(xzqdm) && !string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and xzqdm='" + xzqdm + "' and town='" + towndm + "' ";
                xzqWhere = " and t1.xzqdm='" + xzqdm + "' and t1.town='" + towndm + "' ";
                where = " and xzqdm='" + xzqdm + "' and towndm='" + towndm + "' ";
            }
            else if (!string.IsNullOrEmpty(xzqdm) && string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and xzqdm='" + xzqdm + "' ";
                xzqWhere = " and t1.xzqdm='" + xzqdm +"' ";
                where = " and xzqdm='" + xzqdm + "' ";
            }
            if (!string.IsNullOrEmpty(txtKeyWord))
            {
                strWhere += " and (hzxm like '%" + txtKeyWord + "%' or fwbh like '%" + txtKeyWord + "%' or address like '%" + txtKeyWord + "%')";
            }
            DateTime start = Convert.ToDateTime(startTime);
            DateTime end = Convert.ToDateTime(endTime);
            string toDay = end.ToString("yyyy-MM-dd");
            string row_num_fied = "";
            string row_num_where = "";
            string tablename = "";
            if (startTime == "2024-01-01" && endTime == toDay)
            {
                row_num_fied = "";
                row_num_where = "";
                tablename = "zm_pc_base";
            }
            else if  (start.Year == end.Year && start.Month == end.Month)
            {

                row_num_fied = "";
                row_num_where = " and row_num =1 ";
                tablename = "zm_pc_baselog_"+ end.Month;
            }
            else 
            {
                row_num_fied = " ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,";
                row_num_where = " and row_num =1  ";
                tablename = " zm_pc_baselog ";
            }

                string sql = " ";

            if (category == "1")
            {
                sql = @" 	select ROW_NUMBER() OVER ()index,* from (	SELECT xzq,xzqdm,villagedm,towndm,town,"
            + "    address,hzxm, create_on,village, cs, jzscxyaqcksl,jzxyssltsl,sstdjsqk,xggdjsbp, fwbh FROM ("
            + "      SELECT "+ row_num_fied + "t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town,"
            + "    t1.address,t1.hzxm, t1.create_on,t1.village, t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.sstdjsqk,t1.xggdjsbp, t1.fwbh,hcfwzt,tdsfdb  from "+ tablename + " t1"
            + "    JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh left join (select fwbh,fwzt hcfwzt From zm_pc_base_sqjz where fwzt is not null and yhly='4') t3 on t1.fwbh=t3.fwbh "
            + "    WHERE  1=1 "+ xzqWhere + "   "
            + "     order by t1.village) AS t  where  fwbh not in(select fwbh from zm_pc_base_cj where fwzt is not null)  and hcfwzt is null  and  ( tdsfdb='否')  "
            + "    and 1=1 "	+strWhere	+ row_num_where +" order by village) t  where 1=1   limit " + limit + " offset(" + pageIndex + ") * " + limit;
                }
            else if (category == "2")
            {
         sql = @" 	select ROW_NUMBER() OVER ()index,* from (	SELECT xzq,xzqdm,villagedm,towndm,town,"
             + "    address,hzxm, create_on,village, cs, jzscxyaqcksl,jzxyssltsl,sstdjsqk,xggdjsbp, fwbh FROM ("
             + "      SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town,"
             + "    t1.address,t1.hzxm, t1.create_on,t1.village, t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.sstdjsqk,t1.xggdjsbp, t1.fwbh,hcfwzt  from zm_pc_base t1"
             + "    JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh  left join (select fwbh,fwzt hcfwzt From zm_pc_base_sqjz where fwzt is not null and yhly='4') t3 on t1.fwbh=t3.fwbh "
             + "    WHERE 1=1'  " + xzqWhere + "   "
             + "     order by t1.village) AS t  where row_num =1   and fwbh not in(select fwbh from zm_pc_base_cj where fwzt is not null)    and hcfwzt is null  and   ( sstdjsqk='正在施工改造')  "
             + "    and 1=1 " + strWhere+"order by village) t  where 1=1  limit " + limit + " offset(" + pageIndex + ") * " + limit;
            
        }
            else if (category == "3")
            {
                sql = @" 	select ROW_NUMBER() OVER ()index,* from (	SELECT xzq,xzqdm,villagedm,towndm,town,"
            + "    address,hzxm, create_on,village, cs, jzscxyaqcksl,jzxyssltsl,sstdjsqk,xggdjsbp, fwbh FROM ("
            + "      SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town,"
            + "    t1.address,t1.hzxm, t1.create_on,t1.village, t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.sstdjsqk,t1.xggdjsbp, t1.fwbh,hcfwzt  from zm_pc_base t1"
            + "    JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh left join (select fwbh,fwzt hcfwzt From zm_pc_base_sqjz where fwzt is not null and yhly='4') t3 on t1.fwbh=t3.fwbh"
            + "    WHERE  1=1  " + xzqWhere + "    "
            + "     order by t1.village) AS t  where row_num =1  and fwbh not in(select fwbh from zm_pc_base_cj where fwzt is not null)  and hcfwzt is null   and   ( sstdjsqk='正在制定方案')  "
            + "    and 1=1 " + strWhere+" order by village) t  where 1=1  limit " + limit + " offset(" + pageIndex + ") * " + limit;
            }
            else if (category == "4")
            {
            sql = @" 	select ROW_NUMBER() OVER ()index,* from (	SELECT xzq,xzqdm,villagedm,towndm,town,"
               + "    address,hzxm, create_on,village, cs, jzscxyaqcksl,jzxyssltsl,sstdjsqk,xggdjsbp, fwbh FROM ("
               + "      SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town,"
               + "    t1.address,t1.hzxm, t1.create_on,t1.village, t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.sstdjsqk,t1.xggdjsbp, t1.fwbh,hcfwzt  from zm_pc_base t1"
               + "    JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh  left join (select fwbh,fwzt hcfwzt From zm_pc_base_sqjz where fwzt is not null and yhly='4') t3 on t1.fwbh=t3.fwbh"
               + "    WHERE  1=1  " + xzqWhere + "   "
               + "     order by t1.village) AS t  where row_num =1   and fwbh not in(select fwbh from zm_pc_base_cj where fwzt is not null)  and hcfwzt is null   and  ((xggdjsbp is null OR xggdjsbp=0))  "
               + "    and 1=1 " + strWhere+" order by village) t  where 1=1   limit " + limit + " offset(" + pageIndex + ") * " + limit;
            }
            else if (category == "5")
            {
                sql = @" 	select ROW_NUMBER() OVER ()index,* from (	SELECT xzq,xzqdm,villagedm,towndm,town,"
           + "    address,hzxm, create_on,village, cs, jzscxyaqcksl,jzxyssltsl,sstdjsqk,xggdjsbp, fwbh FROM ("
           + "      SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town,"
           + "    t1.address,t1.hzxm, t1.create_on,t1.village, t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.sstdjsqk,t1.xggdjsbp, t1.fwbh,hcfwzt  from zm_pc_base t1"
           + "    JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh  left join (select fwbh,fwzt hcfwzt From zm_pc_base_sqjz where fwzt is not null and yhly='4') t3 on t1.fwbh=t3.fwbh"
           + "    WHERE  1=1  " + xzqWhere + "  "
           + "     order by t1.village) AS t  where row_num =1   and fwbh not in(select fwbh from zm_pc_base_cj where fwzt is not null)  and hcfwzt is null   and  (cs=2)  "
           + "    and 1=1 " + strWhere+" order by village) t  where 1=1   limit " + limit + " offset(" + pageIndex + ") * " + limit;
           
            }
            else if (category == "6")
            {
                sql = @" 	select ROW_NUMBER() OVER ()index,* from (	SELECT xzq,xzqdm,villagedm,towndm,town,"
          + "    address,hzxm, create_on,village, cs, jzscxyaqcksl,jzxyssltsl,sstdjsqk,xggdjsbp, fwbh FROM ("
          + "      SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town,"
          + "    t1.address,t1.hzxm, t1.create_on,t1.village, t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.sstdjsqk,t1.xggdjsbp, t1.fwbh,hcfwzt  from zm_pc_base t1"
          + "    JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh  left join (select fwbh,fwzt hcfwzt From zm_pc_base_sqjz where fwzt is not null and yhly='4') t3 on t1.fwbh=t3.fwbh"
          + "    WHERE 1=1  " + xzqWhere + "    "
          + "     order by t1.village) AS t  where row_num =1  and fwbh not in(select fwbh from zm_pc_base_cj where fwzt is not null)  and hcfwzt is null  and  (cs>=3)  "
          + "    and 1=1 " + strWhere
          + "  order by village) t  where 1=1   limit " + limit + " offset(" + pageIndex + ") * " + limit;
            }


            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
          || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
          || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            string sql_count = " ";
            if (category == "1")
            {
                sql_count = @" 	select count(1) from (	SELECT xzq,xzqdm,villagedm,towndm,town,"
            + "    address,hzxm, create_on,village, cs, jzscxyaqcksl,jzxyssltsl,sstdjsqk,xggdjsbp, fwbh FROM ("
            + "      SELECT " + row_num_fied + "t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town,"
            + "    t1.address,t1.hzxm, t1.create_on,t1.village, t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.sstdjsqk,t1.xggdjsbp, t1.fwbh,hcfwzt,tdsfdb  from " + tablename + " t1"
            + "    JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh left join (select fwbh,fwzt hcfwzt From zm_pc_base_sqjz where fwzt is not null and yhly='4') t3 on t1.fwbh=t3.fwbh "
            + "    WHERE  1=1 " + xzqWhere + "   "
            + "     order by t1.village) AS t  where  fwbh not in(select fwbh from zm_pc_base_cj where fwzt is not null)  and hcfwzt is null  and  ( tdsfdb='否')  "
            + "    and 1=1 " + strWhere + row_num_where + " order by village) t  where 1=1   ";
            }
            else if (category == "2")
            {
                sql_count = @" 	select count(1) cnt from (	SELECT xzq,xzqdm,villagedm,towndm,town,"
                    + "    address,hzxm, create_on,village, cs, jzscxyaqcksl,jzxyssltsl,sstdjsqk,xggdjsbp, fwbh FROM ("
                    + "      SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town,"
                    + "    t1.address,t1.hzxm, t1.create_on,t1.village, t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.sstdjsqk,t1.xggdjsbp, t1.fwbh,hcfwzt  from zm_pc_base t1"
                    + "    JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh left join (select fwbh,fwzt hcfwzt From zm_pc_base_sqjz where fwzt is not null and yhly='4') t3 on t1.fwbh=t3.fwbh"
                    + "    WHERE  1=1  " + xzqWhere + "   "
                    + "     order by t1.village) AS t  where row_num =1   and fwbh not in(select fwbh from zm_pc_base_cj where fwzt is not null)   and   ( sstdjsqk='正在施工改造')  "
                    + "    and 1=1 " + strWhere + ") t  where 1=1  ";

            }
            else if (category == "3")
            {
                sql_count = @" 	select count(1) cnt from (	SELECT xzq,xzqdm,villagedm,towndm,town,"
            + "    address,hzxm, create_on,village, cs, jzscxyaqcksl,jzxyssltsl,sstdjsqk,xggdjsbp, fwbh FROM ("
            + "      SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town,"
            + "    t1.address,t1.hzxm, t1.create_on,t1.village, t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.sstdjsqk,t1.xggdjsbp, t1.fwbh,hcfwzt  from zm_pc_base t1"
            + "    JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh  left join (select fwbh,fwzt hcfwzt From zm_pc_base_sqjz where fwzt is not null and yhly='4') t3 on t1.fwbh=t3.fwbh"
            + "    WHERE  1=1  " + xzqWhere + "    "
            + "     order by t1.village) AS t  where row_num =1   and fwbh not in(select fwbh from zm_pc_base_cj where fwzt is not null)   and   ( sstdjsqk='正在制定方案')  "
            + "    and 1=1 " + strWhere + ") t  where 1=1  ";
            }
            else if (category == "4")
            {
                sql_count = @" 	select count(1) cnt from (	SELECT xzq,xzqdm,villagedm,towndm,town,"
                   + "    address,hzxm, create_on,village, cs, jzscxyaqcksl,jzxyssltsl,sstdjsqk,xggdjsbp, fwbh FROM ("
                   + "      SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town,"
                   + "    t1.address,t1.hzxm, t1.create_on,t1.village, t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.sstdjsqk,t1.xggdjsbp, t1.fwbh,hcfwzt  from zm_pc_base t1"
                   + "    JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh left join (select fwbh,fwzt hcfwzt From zm_pc_base_sqjz where fwzt is not null and yhly='4') t3 on t1.fwbh=t3.fwbh"
                   + "    WHERE  1=1  " + xzqWhere + "  "
                   + "     order by t1.village) AS t  where row_num =1   and fwbh not in(select fwbh from zm_pc_base_cj where fwzt is not null)   and  ((xggdjsbp is null OR xggdjsbp=0))  "
                   + "    and 1=1 " + strWhere + ") t  where 1=1  ";
            }
            else if (category == "5")
            {
                sql_count = @" 	select count(1) cnt from (	SELECT xzq,xzqdm,villagedm,towndm,town,"
           + "    address,hzxm, create_on,village, cs, jzscxyaqcksl,jzxyssltsl,sstdjsqk,xggdjsbp, fwbh FROM ("
           + "      SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town,"
           + "    t1.address,t1.hzxm, t1.create_on,t1.village, t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.sstdjsqk,t1.xggdjsbp, t1.fwbh,hcfwzt  from zm_pc_base t1"
           + "    JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh left join (select fwbh,fwzt hcfwzt From zm_pc_base_sqjz where fwzt is not null and yhly='4') t3 on t1.fwbh=t3.fwbh"
           + "    WHERE  1=1  " + xzqWhere + "   "
           + "     order by t1.village) AS t  where row_num =1   and fwbh not in(select fwbh from zm_pc_base_cj where fwzt is not null)   and  (cs=2)  "
           + "    and 1=1 " + strWhere + ") t  where 1=1  ";

            }
            else if (category == "6")
            {
                sql_count = @" 	select count(1) cnt from (	SELECT xzq,xzqdm,villagedm,towndm,town,"
          + "    address,hzxm, create_on,village, cs, jzscxyaqcksl,jzxyssltsl,sstdjsqk,xggdjsbp, fwbh FROM ("
          + "      SELECT ROW_NUMBER() OVER (PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.xzq,t1.xzqdm,t1.villagedm,t1.towndm,t1.town,"
          + "    t1.address,t1.hzxm, t1.create_on,t1.village, t1.cs, t1.jzscxyaqcksl,t1.jzxyssltsl,t1.sstdjsqk,t1.xggdjsbp, t1.fwbh,hcfwzt  from zm_pc_base t1"
          + "    JOIN zm_buildings t5 ON t1.fwbh = t5.fwbh left join (select fwbh,fwzt hcfwzt From zm_pc_base_sqjz where fwzt is not null and yhly='4') t3 on t1.fwbh=t3.fwbh"
          + "    WHERE  1=1  " + xzqWhere + "   "
          + "     order by t1.village) AS t  where row_num =1   and fwbh not in(select fwbh from zm_pc_base_cj where fwzt is not null)   and  (cs>=3)  "
          + "    and 1=1 " + strWhere+") t  where 1=1  ";
            }



            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            //执行语句
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, NextPageIndex = pageIndex, PageTotal = count, Total = count });

        }



        /// <summary>
        /// 自查统计分析————根据行政区划统计隐患情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("houses/yhstatic/staticlist")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<Page<object>>), 200)]
        public RespRet<Page<object>> YhStaticList([FromQuery] PagePcztRequestInput pageRequest)
        {
            if (pageRequest.PageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }


            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq xzq = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


            string strWhere = "  ";

            if (user.RoleType == 1 || user.RoleType == 2)
            {
                strWhere += " and xzq!='东城区' and xzq!='西城区' ";
            }
            else if (user.RoleType == 3)
            {
                strWhere += " and xzq='" + xzq.RegionName + "'";
            }
            else
            {
                string xzqdm = xzq.RegionCode.Substring(0, 6);
                strWhere += " and xzqdm='" + xzqdm + "'";
            }

            string sql = @"select ROW_NUMBER() OVER ()index ,* from zcyhgqview  where 1=1 " + strWhere + " order by xzqdm ";
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = list.Count;

            var pageData = new Page<object>(list
                , pageRequest.PageIndex + 1, pageRequest.Limit, count);
            return new RespRet<Page<object>>(true, "Ok", pageData);
        }


        /// <summary>
        /// 自查统计分析————根据乡镇统计隐患情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("houses/yhstatic/TownshipYhstaticlist")]
        public RespRet<Page<object>> TownshipYhstaticlist([FromQuery] string xzqdm, [FromQuery] int pageindex, [FromQuery] int pagesize)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;
            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string strWhere = "";
            if (!string.IsNullOrEmpty(xzqdm))
            {
                if (user.RoleType == 1 || user.RoleType == 2)
                {
                    strWhere += "and xzqdm ='" + xzqdm + "'";
                }
                else if (user.RoleType == 3)
                {
                    strWhere += " and xzq='" + model.RegionName + "'";
                }
                else
                {
                    strWhere += " and towndm='" + model.RegionCode + "'";
                }
            }

            string sql = "select ROW_NUMBER() OVER ()index ,* from zcyhxzview  " +
                "where  1=1 " + strWhere + " limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + "";
            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            string sql_count = "select count(1) cnt from zcyhxzview   " +
                "where  1=1 " + strWhere + "";
            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });

        }
        /// <summary>
        /// 自查统计分析————根据村级统计隐患情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("houses/yhstatic/VillageYhstaticlist")]
        public RespRet<Page<object>> VillageYhstaticlist([FromQuery] string xzq, [FromQuery] string town, [FromQuery] int pageindex, [FromQuery] int pagesize)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            string strWhere = "";
            if (!string.IsNullOrEmpty(xzq + ""))
            {
                strWhere += "and xzq ='" + xzq + "'";
            }
            if (!string.IsNullOrEmpty(town + ""))
            {
                strWhere += "and town ='" + town + "'";
            }
            string sql = "select ROW_NUMBER() OVER ()index ,* from zcyhcjview " +
                "where 1=1 " + strWhere + " order by xzqdm limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + "";

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
               || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
               || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            string sql_count = "select count(1) cnt from zcyhcjview " +
                "where 1=1 " + strWhere + " ";

            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });


        }










        /// <summary>
        /// 根据某个隐患项获取此隐患的房屋
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("houses/yhstatic/yhdetaile")]
        public RespRet<Page<object>> yhdetaile([FromQuery] string check_id, [FromQuery] int pageindex, [FromQuery] int pagesize, [FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string type, [FromQuery] string txtKeyWord)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            string strWhere = "";
            if (!string.IsNullOrEmpty(xzqdm))
            {
                strWhere += " and xzqdm='" + xzqdm + "'";
            }
            if (!string.IsNullOrEmpty(towndm))
            {
                strWhere += " and towndm='" + towndm + "'";
            }
            if (!string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and villagedm='" + villagedm + "'";
            }
            if (!string.IsNullOrEmpty(txtKeyWord))
            {
                strWhere += " and (hzxm like '%"+ txtKeyWord + "%' OR address like '%"+ txtKeyWord + "%' OR fwbh like '%"+ txtKeyWord + "%')";
            }
            if (!string.IsNullOrEmpty(check_id))
            {
                if (check_id != "yhtotal" && check_id != "zgtotal" && check_id != "sytotal") 
                {
                    strWhere += " and check_id='" + check_id + "'";
                }
                
            }
            string sql = "";
            string sql_count = "";
            if (type == "sy")
            {
                sql = "select ROW_NUMBER() OVER ()index ,* from (select m1.*,hzxm,address,'未整改' zgzt from (select * from (select t1.*,fwzt from zm_pc_yhlog_dx t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh) b1 where fwzt is null) m1 left join zm_pc_base t3 on m1.fwbh=t3.fwbh where zg_value='0' order by m1.towndm, m1.villagedm) t where 1=1  " +
                   " " + strWhere + " limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + "";


                sql_count = "select count(1) cnt from (select m1.*,hzxm,address from (select * from (select t1.*,fwzt from zm_pc_yhlog_dx t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh) b1 where fwzt is null) m1 left join zm_pc_base t3 on m1.fwbh=t3.fwbh where zg_value='0' order by m1.towndm, m1.villagedm) t where 1=1   " +
                   "" + strWhere;
            }
            else if (type == "zg")
            {
                sql = "select ROW_NUMBER() OVER ()index ,* from (select m1.*,hzxm,address,'已整改' zgzt from (select * from (select t1.*,fwzt from zm_pc_yhlog_dx t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh) b1 where fwzt is null) m1 left join zm_pc_base t3 on m1.fwbh=t3.fwbh where zg_value='1' order by m1.towndm, m1.villagedm) t where 1=1  " +
                   " " + strWhere + " limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + "";


                sql_count = "select count(1) cnt from (select m1.*,hzxm,address from (select * from (select t1.*,fwzt from zm_pc_yhlog_dx t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh) b1 where fwzt is null) m1 left join zm_pc_base t3 on m1.fwbh=t3.fwbh where zg_value='1' order by m1.towndm, m1.villagedm) t where 1=1   " +
                   "" + strWhere;
            }
            else if (type == "yh")
            {
                sql = "select ROW_NUMBER() OVER ()index ,* from (select m1.*,hzxm,address,case when zg_value=1 then '已整改' else '未整改' end zgzt  from (select * from (select t1.*,fwzt from zm_pc_yhlog_dx t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh) b1 where fwzt is null) m1 left join zm_pc_base t3 on m1.fwbh=t3.fwbh where 1=1  order by m1.towndm, m1.villagedm) t where 1=1  " +
                   " " + strWhere + " limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + "";


                sql_count = "select count(1) cnt from (select m1.*,hzxm,address,case when zg_value=1 then '已整改' else '未整改' end zgzt from (select * from (select t1.*,fwzt from zm_pc_yhlog_dx t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh) b1 where fwzt is null) m1 left join zm_pc_base t3 on m1.fwbh=t3.fwbh where 1=1 order by m1.towndm, m1.villagedm) t where 1=1   " +
                   "" + strWhere;
            }




            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
              || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
              || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }

            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });


        }

        /// <summary>
        /// 获取当前隐患的房屋基本信息
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("houses/yhhourse/detail")]
        [ProducesResponseType(typeof(RespRet<HourseBaseInfo>), 200)]
        public RespRet<HourseBaseInfo> getYhhourseDetail([FromQuery] string id)
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<HourseBaseInfo>(false, "user not logined");
            }

            

            HourseBaseInfo model = new HourseBaseInfo();
            string sql = "  select *,case when (coalesce(yhnum,0) >0) then  CAST('21' AS INTEGER) else  CAST('20' AS INTEGER)  end AS pczt from " +
                "( select b1.*,yhnum from (select t1.id,t2.id yhlog_id,t1.fwbh,t1.yhly,t1.xzqdm,t1.town,t1.village,address,yhfwbh,hzxm,jzrks,zzrs,cs,fjsl,jzfjs,jzscxyaqcksl," +
                "jzxyssltsl,jzscmj,ckfhwggptzlsl,bdfwjzrydddzxcsl,mhqsl,fytsmz,xggdjsbp,jzsyjyplmhxts,jzszlwxdlgybjqs,wdwhjgfjgqsyyrkrgbdj,sstdsyyrkrclzx," +
                "gryssdcsszblyyqksddp,case when cjzwqtjyyt =0 then '否' else cjzwqtjyytmsg end cjzwqtjyytmsg,case when cs=1 and jzscxyaqcksl>=2 then '已达标'  else case when cs>=2 and jzscxyaqcksl>=2  and jzxyssltsl >=2 then '已达标' else sstdjsqk end  end jsrysstd,sstdjsqk,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') pcsj,cjzwqtjyyt,use_electric_bicycle,use_bottled_liquefied_gas,use_stacked_items," +
                "mhqwbcwhyx,jyplmhxtwbcwhyx,ygbjwfwhyx,use_electric_bicycle_photo,use_bottled_liquefied_gas_photo,use_stacked_items_photo,mhqwbcwhyx_photo," +
                "jyplmhxtwbcwhyx_photo,ygbjwfwhyx_photo,use_electric_bicycle_msg,use_bottled_liquefied_gas_msg,use_stacked_items_msg,mhqwbcwhyx_msg," +
                "jyplmhxtwbcwhyx_msg,ygbjwfwhyx_msg,use_electric_bicycle_zgqk,use_bottled_liquefied_gas_zgqk,use_stacked_items_zgqk,mhqwbcwhyx_zgqk," +
                "jyplmhxtwbcwhyx_zgqk,ygbjwfwhyx_msg_zgqk, use_electric_bicycle_zgphoto,use_bottled_liquefied_gas_zgphoto,use_stacked_items_zgphoto," +
                "mhqwbcwhyx_zgphoto,jyplmhxtwbcwhyx_zgphoto,ygbjwfwhyx_msg_zgphoto,slljdx,slljdx_photo, slljdx_msg,slljdx_zgqk,slljdx_zgphoto,zxqlddcsl," +
                "zxqlyhsyqsl,sign,notes from zm_pc_baselog t1, zm_pc_yhlog t2 where t1.id = t2.base_id ) b1 left join (select fwbh,base_id,COALESCE(yhnum,0) yhnum, " +
                "COALESCE(zgnum,0) zgnum from  zm_pc_yhlog t1 ,  (select count(fwbh) yhnum,yhlog_id,sum(case when zg_value='1' then 1 else 0 end) zgnum " +
                "from zm_pc_yhlog_dx  group by yhlog_id) t2 where t1.id=t2.yhlog_id) b2  on b1.id=b2.base_id) t  where yhlog_id ='" + id + "'";

            var item = _dbContext.Database.GetDbConnection().Query<BuildingPcYhLogDetailDto>(sql).ToList();
            List<photoList> photoList = new List<photoList>();
            photoList photo = new photoList();
            if (item != null && item.Count == 1)
            {
                var hourse_item = item[0];//
              
                if (!string.IsNullOrEmpty(hourse_item.use_electric_bicycle_photo))
                {
                    if (hourse_item.use_electric_bicycle_photo.Contains("http"))
                    {
                        string vReplace = hourse_item.use_electric_bicycle_photo.Replace("[\"", "").Replace("\"]", "");
                        photo.use_electric_bicycle_photo_list = vReplace.Split(',');

                    }
                }


                if (!string.IsNullOrEmpty(hourse_item.use_bottled_liquefied_gas_photo))
                {
                    if (hourse_item.use_bottled_liquefied_gas_photo.Contains("http"))
                    {
                        string vReplace = hourse_item.use_bottled_liquefied_gas_photo.Replace("[\"", "").Replace("\"]", "");
                        photo.use_bottled_liquefied_gas_photo_list = vReplace.Split(',');

                    }
                }

                if (!string.IsNullOrEmpty(hourse_item.use_stacked_items_photo))
                {
                    if (hourse_item.use_stacked_items_photo.Contains("http"))
                    {
                        string vReplace = hourse_item.use_stacked_items_photo.Replace("[\"", "").Replace("\"]", "");
                        photo.use_stacked_items_photo_list = vReplace.Split(',');

                    }
                }

                if (!string.IsNullOrEmpty(hourse_item.mhqwbcwhyx_photo))
                {
                    if (hourse_item.mhqwbcwhyx_photo.Contains("http"))
                    {
                        string vReplace = "";
                        vReplace = hourse_item.mhqwbcwhyx_photo.Replace("[\"", "").Replace("\"]", "");
                        photo.mhqwbcwhyx_photo_list = vReplace.Split(',');

                    }
                }

                if (!string.IsNullOrEmpty(hourse_item.jyplmhxtwbcwhyx_photo))
                {
                    if (hourse_item.jyplmhxtwbcwhyx_photo.Contains("http"))
                    {
                        string vReplace = hourse_item.jyplmhxtwbcwhyx_photo.Replace("[\"", "").Replace("\"]", "");
                        photo.jyplmhxtwbcwhyx_photo_list = vReplace.Split(',');

                    }
                }
                if (!string.IsNullOrEmpty(hourse_item.ygbjwfwhyx_photo))
                {
                    if (hourse_item.ygbjwfwhyx_photo.Contains("http"))
                    {
                        string vReplace = hourse_item.ygbjwfwhyx_photo.Replace("[\"", "").Replace("\"]", "");
                        photo.ygbjwfwhyx_photo_list = vReplace.Split(',');

                    }
                }

                photoList.Add(photo);
            }
            model.photoList = photoList;
            model.BuildingPcYhLogDetailDtoList = item;
            ///整改
            string sql_rectification = @"  select *from (select m1.*,check_id,check_name,zg_zp,zg_time,zg_by zg_name   from 
                    (select t1.base_id,t1.fwbh,t2.id dx_id,t1.id yhlog_id from zm_pc_yhlog t1 left join zm_pc_yhlog_dx t2 on t1.id=t2.yhlog_id ) m1 ,
                    zm_pc_yhlog_dxzgson m2  where m1.dx_id=m2.yhlog_dx_id ) t where yhlog_id='" + id + "'";

            var itemLIst = _dbContext.Database.GetDbConnection().Query<RectificationYh>(sql_rectification).ToList();

            model.RectificationYhList = itemLIst;


            return new RespRet<HourseBaseInfo>(true, "Ok", model);
        }




        /// <summary>
        /// 自查统计分析————根据行政区划统计进展情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("houses/evolvestatic/staticlist")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<Page<object>>), 200)]
        public RespRet<Page<object>> EvolvestaticList([FromQuery] PageProgressRequestInput pageRequest)
        {
            if (pageRequest.PageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq xzq = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


            string strWhere = "  ";
            if (user.RoleType == 1 || user.RoleType == 2)
            {
                strWhere += " and xzq!='东城区' and xzq!='西城区' ";
            }
            else if (user.RoleType == 3)
            {
                strWhere += " and xzq='" + xzq.RegionName + "'";
            }
            else
            {
                string xzqdm = xzq.RegionCode.Substring(0, 6);
                strWhere += " and xzqdm='" + xzqdm + "'";
            }
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            if (!string.IsNullOrEmpty(pageRequest.endTime))
            {
                DateTime endData = Convert.ToDateTime(pageRequest.endTime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            else
            {
                DateTime endData = Convert.ToDateTime(today);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }

            string sql = "select ROW_NUMBER() OVER ()index ,* from (select  t1.*,t2.\"orderIdx\" from zcqkgqtext('" + pageRequest.startTime+ "','"+ pageRequest.endTime + " 23:59:59','"+ table_fwzt + "') t1 left join zm_dic_xzq t2 on t1.xzqdm=t2.xzqdm order by t2.\"orderIdx\" ) t  where 1=1 " + strWhere + " ";

            if (pageRequest.cked == "true") 
            {
                sql = @"select ROW_NUMBER() OVER ()index ,* from (select xzq,xzqdm,sum(zcount) zcount,sum(zccount) zccount,sum(wzccount) wzccount 
                    from zcqkcjtext('" + pageRequest.startTime + "', '" + pageRequest.endTime + " 23:59:59','" + table_fwzt + "')  where villagedm in(select villagedm from  zdcmd) group by xzq,xzqdm order by xzqdm) t  ";

            }
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = list.Count;

            var pageData = new Page<object>(list
                , pageRequest.PageIndex + 1, pageRequest.Limit, count);
            return new RespRet<Page<object>>(true, "Ok", pageData);
        }

        /// <summary>
        /// 自查统计分析————根据乡镇统计进展情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("houses/evolvestatic/Townshipstaticlist")]
        public RespRet<Page<object>> Townshipstaticlist([FromQuery] string xzqdm, [FromQuery] int pageindex, [FromQuery] int pagesize, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string cked)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string strWhere = "";
            if (!string.IsNullOrEmpty(xzqdm))
            {
                strWhere += " and xzqdm='" + xzqdm + "'";
            }

            if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime)) 
            {
                startTime= DateTime.Now.ToString("yyyy-MM-dd");
                endTime = DateTime.Now.ToString("yyyy-MM-dd");
            }
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            if (!string.IsNullOrEmpty(endTime))
            {
                DateTime endData = Convert.ToDateTime(endTime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            else
            {
                DateTime endData = Convert.ToDateTime(today);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }

            //string sql = "";
            string sql = "select  ROW_NUMBER() OVER ()index,* from (select *from zcqkxztext('" + startTime + "','" + endTime + " 23:59:59','"+xzqdm+"','"+ table_fwzt + "') where 1 = 1   " + strWhere + "  order by xzqdm,towndm asc) t   limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + "";
           
          //  string sql_count = "";
            string sql_count = "select count(1) cnt from  zcqkxztext('" + startTime + "','" + endTime + " 23:59:59','"+ xzqdm + "','" + table_fwzt + "') where 1 = 1   " + strWhere + " ";
           
            if (cked == "true")
            {
                sql = @"select ROW_NUMBER() OVER ()index ,* from (select xzq,xzqdm,town,towndm,sum(zcount) zcount,sum(zccount) zccount,sum(wzccount) wzccount 
                    from zcqkcjtext('" + startTime + "', '" + endTime + " 23:59:59','" + table_fwzt + "')  where villagedm in(select villagedm from  zdcmd) group by xzq,xzqdm,town,towndm order by towndm) t  " + strWhere;

                sql_count = @"select count(1) cnt from (select xzq,xzqdm,town,towndm,sum(zcount) zcount,sum(zccount) zccount,sum(wzccount) wzccount 
                    from zcqkcjtext('" + startTime + "', '" + endTime + " 23:59:59','" + table_fwzt + "')  where villagedm in(select villagedm from  zdcmd) group by xzq,xzqdm,town,towndm order by towndm) t  where 1=1 " + strWhere;

            }
            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
               || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
               || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });

        }
        /// <summary>
        /// 自查统计分析————根据村级统计进展情况
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("houses/evolvestatic/Villagestaticlist")]
        public RespRet<Page<object>> Villagestaticlist([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] int pageindex, [FromQuery] int pagesize, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string cked)
        {
            if (pageindex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }


            string strWhere = "";
            if (!string.IsNullOrEmpty(xzqdm + ""))
            {
                strWhere += "and xzqdm ='" + xzqdm + "'";
            }
            if (!string.IsNullOrEmpty(towndm + ""))
            {
                strWhere += "and towndm ='" + towndm + "'";
            }
            if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
            {
                startTime = DateTime.Now.ToShortDateString();
                endTime = DateTime.Now.ToShortDateString();
            }
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            if (!string.IsNullOrEmpty(endTime))
            {
                DateTime endData = Convert.ToDateTime(endTime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            else
            {
                DateTime endData = Convert.ToDateTime(today);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            string sql = "select ROW_NUMBER() OVER ()index ,* from (select * from zcqkcjtext('" + startTime+"','"+endTime+" 23:59:59','"+xzqdm+"','"+towndm+"','"+ table_fwzt + "')  where 1=1 " + strWhere + " order by xzqdm,towndm asc ,villagedm asc) t  limit " + pagesize + " offset (" + pageindex + " - 1) * " + pagesize + "";
            string sql_count = "select count(1) cnt from zcqkcjtext('" + startTime + "','" + endTime + " 23:59:59','"+xzqdm+"','"+towndm+"','" + table_fwzt + "')  where 1=1 " + strWhere + " ";
            if (cked == "true")
            {
                sql = @"select ROW_NUMBER() OVER ()index ,* from (select * 
                    from zcqkcjtext('" + startTime + "', '" + endTime + " 23:59:59','" + table_fwzt + "')  where villagedm in(select villagedm from  zdcmd) order by villagedm) t  " + strWhere;

                sql_count = @"select count(1) cnt from (select *
                    from zcqkcjtext('" + startTime + "', '" + endTime + " 23:59:59','" + table_fwzt + "')  where villagedm in(select villagedm from  zdcmd) order by villagedm) t  where 1=1 " + strWhere;

            }

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
               || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
               || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            
            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var datas = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = datas, NextPageIndex = pageindex + 1, PageTotal = count, Total = count });
        }

        /// <summary>
        /// 村级工作进展统计
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("houses/evolvestatic/villagePostStatic")]
        public RespRet<Page<object>> villagePostStatic([FromQuery] int pageIndex, [FromQuery] int limit, [FromQuery] string districts, [FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery]  string startTime, [FromQuery]  string endTime, [FromQuery] string cked,[FromQuery] string iszdc)
        {
            if (pageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }

            var sbSql = new StringBuilder();
            var sbSqlWhere = new StringBuilder();
            var sqlParams = new List<NpgsqlParameter>();

            var sbSqlBuildingEx = new StringBuilder();

            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


            string strWhere = "  ";

            if (user.RoleType == 1 || user.RoleType == 2)
            {
                strWhere += " and xzq!='东城区' and xzq!='西城区' ";
            }
            else if (user.RoleType == 3)  //区级
            {
                strWhere += " and xzqdm='" + model.RegionCode + "'";
            }
            else if (user.RoleType == 4)
            {
                string tdm = model.RegionCode;
                if (model.RegionCode.Length != 12)
                {
                    tdm = model.RegionCode + "000";
                }
                strWhere += " and towndm='" + tdm + "'";
            }
            else if (user.RoleType == 5)
            {
                strWhere += " and villagedm='" + model.RegionCode + "'";
            }
            if (!string.IsNullOrEmpty(xzqdm))
            {
                strWhere += " and xzqdm='" + xzqdm + "'";
            }
            if (!string.IsNullOrEmpty(towndm))
            {
                strWhere += " and towndm='" + towndm + "'";
            }
            if (!string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and villagedm='" + villagedm + "'";
            }



            //行政区划
            if (!string.IsNullOrEmpty(districts))
            {
                var districtCodeWhere = Utilitys.GetLikeSqlWhere(JsonConvert.SerializeObject(districts), "");
                if (!string.IsNullOrEmpty(districtCodeWhere))
                {
                    strWhere += " and  " + districtCodeWhere;
                }
            }


            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string sql = "";
            string sql_count = "";

            string table_fwzt = "";
            if (!string.IsNullOrEmpty(endTime))
            {
                DateTime endData = Convert.ToDateTime(endTime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
                if (Convert.ToDateTime(endTime) > DateTime.Now)
                {
                    table_fwzt = "zm_building_fwzt_" + DateTime.Now.Year;
                }
            }
            else
            {
                DateTime endData = Convert.ToDateTime(today);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }

            if (startTime == today && endTime == today)
            {
                sql = @"select ROW_NUMBER() OVER ()index,*,case when yhcount=0 and zgcount=0 then '100.00%' else  concat(
        CASE
            WHEN (round((((zgcount)::numeric / yhcount) * (100)::numeric), 2) > (100)::numeric) THEN 100.00
            ELSE round((((zgcount)::numeric / yhcount) * (100)::numeric), 2)
        END, '%') end zgl from zcqkcjtext('" + startTime + " 00:00:00', ' " + endTime + " 23:59:59', '"+ table_fwzt + "') where 1=1 " + strWhere + " order by xzqdm,towndm asc ,villagedm asc limit " + limit + " offset (" + pageIndex + ") * " + limit + "";
                sql_count = @"select count(1) cnt from zcqkcjtext('" + startTime + " 00:00:00', ' " + endTime + " 23:59:59', '"+ table_fwzt + "') where 1=1 " + strWhere;
            }
            else
            {
                string startimemonth = Convert.ToDateTime(startTime).ToString("yyyy_MM");
                string endmonth = Convert.ToDateTime(endTime).ToString("yyyy_MM");
                string month = "";
                if (startimemonth == endmonth)
                {
                    month = startimemonth;
                    sql = @"select ROW_NUMBER() OVER ()index,*,case when yhcount=0 and zgcount=0 then '100.00%' else  concat(
        CASE
            WHEN (round((((zgcount)::numeric / yhcount) * (100)::numeric), 2) > (100)::numeric) THEN 100.00
            ELSE round((((zgcount)::numeric / yhcount) * (100)::numeric), 2)
        END, '%') end zgl from zcqkcjtext('" + startTime + " 00:00:00','" +endTime + " 23:59:59' , '" + table_fwzt + "') where 1=1 " + strWhere + " order by xzqdm,towndm asc ,villagedm asc limit " + limit + " offset (" + pageIndex + ") * " + limit + "";
                    sql_count = @"select count(1) cnt from zcqkcjtext('" + startTime + " 00:00:00', ' " + endTime + " 23:59:59', '" + table_fwzt + "') where 1=1 " + strWhere;
                }
                else
                {

                    sql = @"select ROW_NUMBER() OVER ()index,*,case when yhcount=0 and zgcount=0 then '100.00%' else  concat(
        CASE
            WHEN (round((((zgcount)::numeric / yhcount) * (100)::numeric), 2) > (100)::numeric) THEN 100.00
            ELSE round((((zgcount)::numeric / yhcount) * (100)::numeric), 2)
        END, '%') end zgl from zcqkcjtext('" + startTime + " 00:00:00','" + endTime + " 23:59:59', '"+ table_fwzt + "')  where 1=1 " + strWhere + " order by xzqdm,towndm asc ,villagedm asc limit " + limit + " offset (" + pageIndex + ") * " + limit + "";
                    sql_count = @"select count(1) cnt from zcqkcjtext('" + startTime + " 00:00:00', ' " + endTime + " 23:59:59', '"+ table_fwzt + "') where 1=1 " + strWhere;
                }
            }

            if (cked == "true") 
            {
                sql = @"select ROW_NUMBER() OVER ()index,*  from (select t1.xzq,t1.townold town,t1.villageold village,COALESCE(zcount, 0) zcount,COALESCE(zccount, 0) zccount,COALESCE(wzccount, 0) wzccount,COALESCE(zcl::text
, '0%') zcl,COALESCE(yhcount, 0) yhcount,COALESCE(zgcount, 0) zgcount,case when yhcount=0 and zgcount=0 then '100.00%' else  concat(
        CASE
            WHEN (round((((zgcount)::numeric / yhcount) * (100)::numeric), 2) > (100)::numeric) THEN 100.00
            ELSE round((((zgcount)::numeric / yhcount) * (100)::numeric), 2)
        END, '%') end zgl,COALESCE(syyhcount, 0) syyhcount,t2.xzqdm,t2.towndm,t1.villagedm from zdcmd t1 left join  zcqkcjtext('" + startTime + "','" + endTime + "', '"+ table_fwzt + "') t2 on t1.villagedm=t2.villagedm) t where 1 = 1 and xzq!= '东城区' and xzq!= '西城区'  " + strWhere + "  order by xzqdm,towndm asc ,villagedm asc limit " + limit + " offset (" + pageIndex + ") * " + limit + "";
                sql_count = @"select count(1) cnt from (select t1.xzq,t1.townold town,t1.villageold village,zcount,zccount,wzccount,zcl,yhcount,zgcount,syyhcount,t2.xzqdm,t2.towndm,t1.villagedm from zdcmd t1 left join  zcqkcjtext('" + startTime + "','" + endTime + "','"+ table_fwzt +"') t2 on t1.villagedm=t2.villagedm) t where 1 = 1 and xzq!= '东城区' and xzq!= '西城区'  " + strWhere + " ";
            }

             if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase) 
           || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
           || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            
            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            //执行语句
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);
            var pageData = new Page<object>(list
                , pageIndex + 1,limit, count);
            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, NextPageIndex = pageIndex + 1, PageTotal = count, Total = count });

        }


        /// <summary>
        /// 自查明细
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("houses/evolvestatic/villagePostStaticDetail")]
        public RespRet<Page<object>> villagePostStaticDetail([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string category, [FromQuery] int pageIndex, [FromQuery] int pageSize, [FromQuery] string endTime, [FromQuery] string  startTime, [FromQuery] string txtKeyWord, [FromQuery] string cs)
        {
            if (pageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }

            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }

            var sbSql = new StringBuilder();
            var sbSqlWhere = new StringBuilder();
            var sqlParams = new List<NpgsqlParameter>();

            var sbSqlBuildingEx = new StringBuilder();

            string district = user.District;

            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string strWhere = " ";
            string xzqwhere = "";
            if (!string.IsNullOrEmpty(txtKeyWord))
            {
                xzqwhere += " and (hzxm like '%" + txtKeyWord + "%' or fwbh like '%" + txtKeyWord + "%' or address like '%" + txtKeyWord + "%')";
            }
            
           
            if (!string.IsNullOrEmpty(xzqdm))
            {
                strWhere += " and xzqdm='" + xzqdm + "' ";
                xzqwhere += " and xzqdm='" + xzqdm + "' ";
            }
            if (!string.IsNullOrEmpty(towndm))
            {
                strWhere += "  and town='" + towndm + "' ";
                xzqwhere += " and towndm='" + towndm + "' ";
            }
            if (!string.IsNullOrEmpty(villagedm))
            {
                strWhere += " and village='" + villagedm + "'";
                xzqwhere += " and villagedm='" + villagedm + "' ";
            }
            if (string.IsNullOrEmpty(startTime) && !string.IsNullOrEmpty(endTime) )
            {
                startTime = "2024-01-01";
                endTime = DateTime.Now.ToString("yyyy-MM-dd");
            }
            string sql = " ";
            string today = DateTime.Now.ToString("yyyy-MM-dd");
            string table_fwzt = "";
            if (!string.IsNullOrEmpty(endTime))
            {
                DateTime endData = Convert.ToDateTime(endTime);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            else
            {
                DateTime endData = Convert.ToDateTime(today);
                table_fwzt = "zm_building_fwzt_" + endData.Year;
            }
            if (!string.IsNullOrEmpty(cs))
            {
                if (cs.Contains("3"))
                {
                    string[] arrr = cs.Split(',');
                    if (arrr.Length == 2)
                    {
                        string cssql = "";
                        for (int i = 0; i < arrr.Length; i++)
                        {
                            if (arrr[i] == "3")
                            {
                                cssql += "  cs >=3 OR ";
                            }
                            if (arrr[i] == "1")
                            {
                                cssql += "  cs=1 OR ";
                            }
                            if (arrr[i] == "2")
                            {
                                cssql += " cs =2 OR ";
                            }
                        }

                        strWhere += " and (" + (cssql.Substring(0, cssql.Length - 3)) + ")";
                    }
                    else
                    {
                        strWhere += " and cs >=" + cs;
                    }
                }
                else
                {
                    strWhere += " and cs in(" + cs + ")";
                }
            }
            DateTime start = Convert.ToDateTime(startTime);
            DateTime end = Convert.ToDateTime(endTime);




            if (category == "yzc")
            {
                
                //if (start.Year == end.Year && start.Month == end.Month)
                //{
                //    sql = @"select ROW_NUMBER() OVER ()index ,* from (select ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.* from zm_pc_baselog_" + end.Year+"_0"+end.Month + " t1,zm_pc_yhlog_" + end.Year + "_0" + end.Month + " t2 where t1.id=t2.base_id and  t2.create_on BETWEEN '" + startTime +"' and '" + endTime + " 23:59:59' ) t1 " +
                //  "    where   row_num=1  and fwbh not in " +
                //  " (select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59') " + strWhere + "limit " + pageSize + " offset(" + pageIndex + ") * " + pageSize;
                //}
                //else if (start.Year == end.Year && (end.Month - start.Month) == 1)
                //{
                //    sql = @"select ROW_NUMBER() OVER ()index ,* from (select ROW_NUMBER() OVER(PARTITION BY fwbh ORDER BY create_on DESC) AS rn,* from (select *from (select ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) "
                //        + "    AS row_num,t1.* from zm_pc_baselog_" + end.Year + "_0" + end.Month + " t1,zm_pc_yhlog_" +end.Year + "_0" + end.Month + " t2 where t1.id=t2.base_id and t2.create_on <= '" + endTime+" 23:59:59') t1 "
                //       + "    where  row_num=1  and fwbh not in  (select fwbh from "+ table_fwzt + " where final_fwzt IS NOT NULL AND "
                //       + " final_create_on = '" + endTime+" 23:59:59')   "+ strWhere
                //       + "  union all "
                //       + " select *from (select ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.* from zm_pc_baselog_" + start.Year + "_0" + start.Month + " t1,zm_pc_yhlog_" + start.Year + "_0" + start.Month + " t2 where t1.id=t2.base_id and  t2.create_on  >= '"+ startTime + "'   ) t1  "
                //       + " where   row_num=1  and fwbh not in  (select fwbh from "+ table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '"+endTime+" 23:59:59')   "
                //       + strWhere+ "     ) t order by create_on desc) t  where rn=1 "
                //       + " limit " + pageSize + " offset(" + pageIndex + ") * " + pageSize;
                //}
                //else
                {
                    sql = @"select ROW_NUMBER() OVER ()index ,* from (select ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.* from zm_pc_baselog t1,zm_pc_yhlog t2 where t1.id=t2.base_id and t2.create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59' ) t1 " +
             "    where   row_num=1  and fwbh not in " +
             " (select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59') " + strWhere + "limit " + pageSize + " offset(" + pageIndex + ") * " + pageSize;
                }
              
            }
            else if (category == "wzc") 
            {

                //if (start.Year== end.Year && start.Month == end.Month)
                //{
                
                //    sql = @" select  ROW_NUMBER() OVER ()index,*  from (select t1.xzq,t1.xzqdm,t1.town,t1.towndm,t1.villagedm,t1.village,t1.hzxm,t1.address,t1.fwbh  " +
                //     " from zm_buildings t1 where  fwbh not in(select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59') " +
                //    "  and fwbh not in( " +
                //    " select fwbh from (select ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.* from zm_pc_baselog_" + end.Year + "_0" + end.Month + " t1,zm_pc_yhlog_" + end.Year + "_0" + end.Month + " t2 where t1.id=t2.base_id and t2.create_on BETWEEN '" + startTime +"' and '" + endTime + " 23:59:59'  ) t1 " +
                //  "    where  row_num=1  and fwbh not in " +
                //  " (select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59') " + strWhere +")  order by village ) t where 1=1  " + xzqwhere+
                //    " limit " + pageSize + " offset(" + pageIndex + ") * " + pageSize;
                //}
                //else if (start.Year == end.Year && (end.Month - start.Month) == 1)
                //{
                //    sql = @"select  ROW_NUMBER() OVER ()index,*  from (select t1.xzq,t1.xzqdm,t1.town,t1.towndm,t1.villagedm,t1.village,t1.hzxm,t1.address,t1.fwbh  " +
                //     " from zm_buildings t1 where  fwbh not in(select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59') " +
                //    "  and fwbh not in( select fwbh from (select ROW_NUMBER() OVER(PARTITION BY fwbh ORDER BY create_on DESC) AS rn,* from (select *from (select ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) "
                //        + "    AS row_num,t1.* from zm_pc_baselog_" + end.Year + "_0" + end.Month + " t1,zm_pc_yhlog_" + end.Year + "_0" + end.Month + " t2 where t1.id=t2.base_id and t2.create_on <= '" + endTime + " 23:59:59') t1 "
                //       + "    where   row_num=1  and fwbh not in  (select fwbh from "+ table_fwzt + " where final_fwzt IS NOT NULL AND "
                //       + " final_create_on = '" + endTime + " 23:59:59')   " + strWhere
                //       + "  union all "
                //       + " select *from (select ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.* from zm_pc_baselog_" + start.Year + "_0" + start.Month + " t1,zm_pc_yhlog_" + start.Year + "_0" + start.Month + " t2 where t1.id=t2.base_id and  t2.create_on  >= '" + startTime + "') t1  "
                //       + " where  row_num=1  and fwbh not in  (select fwbh from "+ table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59')   "
                //       + strWhere + "     ) t order by create_on desc) t  where rn=1 )  order by village ) t where 1=1 "+ xzqwhere
                //       + " limit " + pageSize + " offset(" + pageIndex + ") * " + pageSize;
                //}
                //else 
                {
                    sql = @"select  ROW_NUMBER() OVER ()index,*  from (select t1.xzq,t1.xzqdm,t1.town,t1.towndm,t1.villagedm,t1.village,t1.hzxm,t1.address,t1.fwbh  " +
                     " from zm_buildings t1 where  fwbh not in(select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59') " +
                    "  and fwbh not in(select fwbh from (select ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.* from zm_pc_baselog t1,zm_pc_yhlog t2 where t1.id=t2.base_id and  t2.create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59' ) t1 " +
             "    where  row_num=1  and fwbh not in " +
             " (select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59') " + strWhere + ")  order by village ) t where  1=1  " + xzqwhere + " limit " + pageSize + " offset(" + pageIndex + ") * " + pageSize;
                }

                
            }
            

            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
          || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
          || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }


            string sql_count = " ";

            if (category == "yzc")
            {
                //if (start.Year == end.Year &&  start.Month == end.Month)
                //{
                //    sql_count = @"select count(1) cnt from (select ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.* from zm_pc_baselog_" + end.Year + "_0" + end.Month + " t1,zm_pc_yhlog_" + end.Year + "_0" + end.Month + " t2 where t1.id=t2.base_id  and t2.create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59' ) t1 " +
                //  "    where  row_num=1  and fwbh not in " +
                //  " (select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59') " + strWhere;
                //}
                //else if (start.Year == end.Year &&  (end.Month - start.Month) == 1)
                //{
                //    sql_count = @"select count(1) cnt from (select ROW_NUMBER() OVER(PARTITION BY fwbh ORDER BY create_on DESC) AS rn,* from (select *from (select ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) "
                //        + "    AS row_num,t1.* from zm_pc_baselog_" + end.Year + "_0" + end.Month + " t1,zm_pc_yhlog_" + end.Year + "_0" + end.Month + " t2 where t1.id=t2.base_id and  t2.create_on <= '" + endTime + " 23:59:59'  ) t1 "
                //       + "    where   row_num=1  and fwbh not in  (select fwbh from "+ table_fwzt + " where final_fwzt IS NOT NULL AND "
                //       + " final_create_on = '" + endTime + " 23:59:59')    "+ strWhere
                //       + "  union all "
                //       + " select *from (select ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.* from zm_pc_baselog_" + start.Year + "_0" + start.Month + " t1,zm_pc_yhlog_" + start.Year + "_0" + start.Month + " t2 where t1.id=t2.base_id  and  t2.create_on  >= '" + startTime + "'  ) t1  "
                //       + " where  row_num=1  and fwbh not in  (select fwbh from "+ table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59')   "
                //       + strWhere + "     ) t order by create_on desc) t  where rn=1 ";
                //}
                //else 
                {
                    sql_count = @"select count(1) cnt from (select ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.* from zm_pc_baselog t1,zm_pc_yhlog t2 where t1.id=t2.base_id and t2.create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59' ) t1 " +
             "    where   row_num=1  and fwbh not in " +
             " (select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59') " + strWhere ;
                }
            }
            else if (category == "wzc")
            {
                //if (start.Year == end.Year &&  start.Month == end.Month)
                //{

                //    sql_count = @" select   count(1) cnt from (select t1.xzq,t1.xzqdm,t1.town,t1.towndm,t1.villagedm,t1.village,t1.hzxm,t1.address,t1.fwbh  " +
                //     " from zm_buildings t1 where  fwbh not in(select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59') " +
                //    "  and fwbh not in( " +
                //    " select fwbh from (select ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.* from zm_pc_baselog_" + end.Year + "_0" + end.Month + " t1,zm_pc_yhlog_" + end.Year + "_0" + end.Month + " t2 where t1.id=t2.base_id and t2.create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59'  ) t1 " +
                //  "    where  row_num=1  and fwbh not in " +
                //  " (select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59') " + strWhere + ")  order by village ) t   where 1=1  " + xzqwhere;
                //}
                //else if (start.Year == end.Year &&  (end.Month - start.Month) == 1)
                //{
                //    sql_count = @"select  count(1) cnt from (select t1.xzq,t1.xzqdm,t1.town,t1.towndm,t1.villagedm,t1.village,t1.hzxm,t1.address,t1.fwbh  " +
                //     " from zm_buildings t1 where  fwbh not in(select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59') " +
                //    "  and fwbh not in( select fwbh from (select ROW_NUMBER() OVER(PARTITION BY fwbh ORDER BY create_on DESC) AS rn,* from (select *from (select ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) "
                //        + "    AS row_num,t1.* from zm_pc_baselog_" + end.Year + "_0" + end.Month + " t1,zm_pc_yhlog_" + end.Year + "_0" + end.Month + " t2 where t1.id=t2.base_id  and t2.create_on <= '" + endTime + " 23:59:59' ) t1 "
                //       + "    where   row_num=1  and fwbh not in  (select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND "
                //       + " final_create_on = '" + endTime + " 23:59:59')   " + strWhere
                //       + "  union all "
                //       + " select *from (select ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.* from zm_pc_baselog_" + start.Year + "_0" + start.Month + " t1,zm_pc_yhlog_" + start.Year + "_0" + start.Month + " t2 where t1.id=t2.base_id and t2.create_on  >= '" + startTime +"'  ) t1  "
                //       + " where  row_num=1  and fwbh not in  (select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59')   "
                //       + strWhere + "     ) t order by create_on desc) t  where rn=1 )  order by village ) t where 1=1 " + xzqwhere;
                //}
                //else 
                {
                    sql_count = @"select  count(1) cnt  from (select t1.xzq,t1.xzqdm,t1.town,t1.towndm,t1.villagedm,t1.village,t1.hzxm,t1.address,t1.fwbh  " +
                     " from zm_buildings t1 where  fwbh not in(select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59') " +
                    "  and fwbh not in(select fwbh from (select ROW_NUMBER() OVER(PARTITION BY t1.fwbh ORDER BY t1.create_on DESC) AS row_num,t1.* from zm_pc_baselog t1,zm_pc_yhlog t2 where t1.id=t2.base_id  and t2.create_on BETWEEN '" + startTime + "' and '" + endTime + " 23:59:59' ) t1 " +
             "    where   row_num=1  and fwbh not in " +
             " (select fwbh from " + table_fwzt + " where final_fwzt IS NOT NULL AND final_create_on = '" + endTime + " 23:59:59') " + strWhere + ")  order by village ) t where  1=1  " + xzqwhere;
                }
            }
            


            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
                || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            //执行语句
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, NextPageIndex = pageIndex, PageTotal = count, Total = count });

        }

        /// <summary>
        /// 获取当前房屋的检查记录中的详细信息
        /// </summary>
        /// <param name="id">记录ID</param>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("houses/{id}/restinfo/")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<HourseBaseInfo>), 200)]
        public RespRet<HourseBaseInfo> restinfo([FromRoute] string id)
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<HourseBaseInfo>(false, "user not logined");
            }

            HourseBaseInfo model = new HourseBaseInfo();
            string sql = @"  select  * ,case when (coalesce(yhnum,0) >0) then  CAST('21' AS INTEGER) else  CAST('20' AS INTEGER)  end AS pczt from 
                ( select b1.*,yhnum from (select   ST_X(ST_Centroid(t3.geom)) AS longitude,  -- 移除了ST_Collect()
      ST_Y(ST_Centroid(t3.geom)) AS latitude, t1.id,t1.fwbh,t1.yhly,t1.xzqdm,t1.town,t1.village,t3.address,t3.yhfwbh,t3.hzxm,t1.jzrks,t1.zzrs,t1.cs,t1.fjsl,t1.jzfjs,t1.jzscxyaqcksl,
                t1.jzxyssltsl,t1.jzscmj,ckfhwggptzlsl,bdfwjzrydddzxcsl,mhqsl,fytsmz,xggdjsbp,t1.jzsyjyplmhxts,t1.jzszlwxdlgybjqs,wdwhjgfjgqsyyrkrgbdj,sstdsyyrkrclzx,
                gryssdcsszblyyqksddp,case when cjzwqtjyyt =0 then '否' else cjzwqtjyytmsg end cjzwqtjyytmsg,case when t1.cs=1 and t1.jzscxyaqcksl>=2 then '已达标'  else case when t1.cs>=2 and t1.jzscxyaqcksl>=2  and t1.jzxyssltsl >=2 then '已达标' else sstdjsqk end  end jsrysstd,sstdjsqk,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') pcsj,cjzwqtjyyt,use_electric_bicycle,use_bottled_liquefied_gas,use_stacked_items, 
                mhqwbcwhyx,jyplmhxtwbcwhyx,ygbjwfwhyx,use_electric_bicycle_photo,use_bottled_liquefied_gas_photo,use_stacked_items_photo,mhqwbcwhyx_photo, 
                jyplmhxtwbcwhyx_photo,ygbjwfwhyx_photo,use_electric_bicycle_msg,use_bottled_liquefied_gas_msg,use_stacked_items_msg,mhqwbcwhyx_msg, 
                jyplmhxtwbcwhyx_msg,ygbjwfwhyx_msg,use_electric_bicycle_zgqk,use_bottled_liquefied_gas_zgqk,use_stacked_items_zgqk,mhqwbcwhyx_zgqk, 
                jyplmhxtwbcwhyx_zgqk,ygbjwfwhyx_msg_zgqk, use_electric_bicycle_zgphoto,use_bottled_liquefied_gas_zgphoto,use_stacked_items_zgphoto, 
                mhqwbcwhyx_zgphoto,jyplmhxtwbcwhyx_zgphoto,ygbjwfwhyx_msg_zgphoto,slljdx,slljdx_photo, slljdx_msg,slljdx_zgqk,slljdx_zgphoto,zxqlddcsl, 
                zxqlyhsyqsl,sign,notes,t1.tdsfdb,t1.hzxm create_name,t1.create_on from zm_pc_baselog t1, zm_pc_yhlog t2,zm_buildings t3 where t1.id = t2.base_id and t1.fwbh=t3.fwbh ) b1 left join (select fwbh,base_id,COALESCE(yhnum,0) yhnum,  
                COALESCE(zgnum,0) zgnum from  zm_pc_yhlog t1 ,  (select count(fwbh) yhnum,yhlog_id,sum(case when zg_value='1' then 1 else 0 end) zgnum  
                from zm_pc_yhlog_dx  group by yhlog_id) t2 where t1.id=t2.yhlog_id) b2  on b1.id=b2.base_id) t  where id ='" + id + "'";
            var item = _dbContext.Database.GetDbConnection().Query<BuildingPcYhLogDetailDto>(sql).ToList();
            model.BuildingPcYhLogDetailDtoList = item;
            ///整改
            string sql_rectification = @"  select *from (select m1.*,check_id,check_name,zg_zp,zg_time,zg_by zg_name   from 
                    (select t1.base_id,t1.fwbh,t2.id dx_id from zm_pc_yhlog t1 left join zm_pc_yhlog_dx t2 on t1.id=t2.yhlog_id ) m1 ,
                    zm_pc_yhlog_dxzgson m2  where m1.dx_id=m2.yhlog_dx_id ) t where base_id='" + id + "'";

            var itemLIst = _dbContext.Database.GetDbConnection().Query<RectificationYh>(sql_rectification).ToList();
            model.RectificationYhList = itemLIst;
            return new RespRet<HourseBaseInfo>(true, "Ok", model);
        }

        /// <summary>
        /// 获取当前房屋的检查记录中的详细信息
        /// </summary>
        /// <param name="id">记录ID</param>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("houses/{fwbh}/housrApi/")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<HourseBaseInfo>), 200)]
        public RespRet<HourseBaseInfo> housrApi([FromRoute] string fwbh)
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<HourseBaseInfo>(false, "user not logined");
            }

            HourseBaseInfo model = new HourseBaseInfo();
            string sql = "  select *,case when (coalesce(yhnum,0) >0) then  CAST('21' AS INTEGER) else  CAST('20' AS INTEGER)  end AS pczt from " +
                "( select b1.*,yhnum from (select t1.id,t1.fwbh,t1.yhly,t1.xzqdm,t1.town,t1.village,address,yhfwbh,hzxm,jzrks,zzrs,cs,fjsl,jzfjs,jzscxyaqcksl," +
                "jzxyssltsl,jzscmj,ckfhwggptzlsl,bdfwjzrydddzxcsl,mhqsl,fytsmz,xggdjsbp,jzsyjyplmhxts,jzszlwxdlgybjqs,wdwhjgfjgqsyyrkrgbdj,sstdsyyrkrclzx," +
                "gryssdcsszblyyqksddp,case when cjzwqtjyyt =0 then '否' else cjzwqtjyytmsg end cjzwqtjyytmsg,case when cs=1 and jzscxyaqcksl>=2 then '已达标'  else case when cs>=2 and jzscxyaqcksl>=2  and jzxyssltsl >=2 then '已达标' else sstdjsqk end  end jsrysstd,sstdjsqk,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') pcsj,cjzwqtjyyt,use_electric_bicycle,use_bottled_liquefied_gas,use_stacked_items," +
                "mhqwbcwhyx,jyplmhxtwbcwhyx,ygbjwfwhyx,use_electric_bicycle_photo,use_bottled_liquefied_gas_photo,use_stacked_items_photo,mhqwbcwhyx_photo," +
                "jyplmhxtwbcwhyx_photo,ygbjwfwhyx_photo,use_electric_bicycle_msg,use_bottled_liquefied_gas_msg,use_stacked_items_msg,mhqwbcwhyx_msg," +
                "jyplmhxtwbcwhyx_msg,ygbjwfwhyx_msg,use_electric_bicycle_zgqk,use_bottled_liquefied_gas_zgqk,use_stacked_items_zgqk,mhqwbcwhyx_zgqk," +
                "jyplmhxtwbcwhyx_zgqk,ygbjwfwhyx_msg_zgqk, use_electric_bicycle_zgphoto,use_bottled_liquefied_gas_zgphoto,use_stacked_items_zgphoto," +
                "mhqwbcwhyx_zgphoto,jyplmhxtwbcwhyx_zgphoto,ygbjwfwhyx_msg_zgphoto,slljdx,slljdx_photo, slljdx_msg,slljdx_zgqk,slljdx_zgphoto,zxqlddcsl," +
                "zxqlyhsyqsl,sign,notes,t1.tdsfdb,t1.hzxm create_name,t1.create_on from zm_pc_base t1, zm_pc_yh t2 where t1.id = t2.base_id ) b1 left join (select fwbh,base_id,COALESCE(yhnum,0) yhnum, " +
                "COALESCE(zgnum,0) zgnum from  zm_pc_yhlog t1 ,  (select count(fwbh) yhnum,yhlog_id,sum(case when zg_value='1' then 1 else 0 end) zgnum " +
                "from zm_pc_yhlog_dx  group by yhlog_id) t2 where t1.id=t2.yhlog_id) b2  on b1.id=b2.base_id) t  where fwbh ='" + fwbh + "'";
            var item = _dbContext.Database.GetDbConnection().Query<BuildingPcYhLogDetailDto>(sql).ToList();
            model.BuildingPcYhLogDetailDtoList = item;
            return new RespRet<HourseBaseInfo>(true, "Ok", model);
        }




        /// <summary>
        /// 获取当前房屋的检查记录中的详细信息
        /// </summary>
        /// <param name="id">记录ID</param>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet("houses/{id}/checkmsg/")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<HourseBaseInfo>), 200)]
        public RespRet<HourseBaseInfo> checkmsg([FromRoute] string id)
        {
            var userNumber = this.CurrentUser();
            //if (string.IsNullOrEmpty(userNumber))
            //{
            //    return new RespRet<HourseBaseInfo>(false, "user not logined");
            //}

            HourseBaseInfo model = new HourseBaseInfo();
            string sql = "  select * from " +
                "( select b1.*,yhnum from (select case when  t2.pczt ='1' then '无隐患' when t2.pczt ='2' then '有隐患' end pczt, t1.id,t1.fwbh,t1.yhly,t1.xzqdm,t1.town,t1.village,address,yhfwbh,hzxm,jzrks,zzrs,cs,fjsl,jzfjs,jzscxyaqcksl," +
                "jzxyssltsl,jzscmj,ckfhwggptzlsl,bdfwjzrydddzxcsl,mhqsl,fytsmz,xggdjsbp,jzsyjyplmhxts,jzszlwxdlgybjqs,wdwhjgfjgqsyyrkrgbdj,sstdsyyrkrclzx," +
                "gryssdcsszblyyqksddp,case when cjzwqtjyyt =0 then '否' else cjzwqtjyytmsg end cjzwqtjyytmsg,case when cs=1 and jzscxyaqcksl>=2 then '已达标'  else case when cs>=2 and jzscxyaqcksl>=2  and jzxyssltsl >=2 then '已达标' else sstdjsqk end  end jsrysstd,sstdjsqk,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') pcsj,cjzwqtjyyt,use_electric_bicycle,use_bottled_liquefied_gas,use_stacked_items," +
                "mhqwbcwhyx,jyplmhxtwbcwhyx,ygbjwfwhyx,use_electric_bicycle_photo,use_bottled_liquefied_gas_photo,use_stacked_items_photo,mhqwbcwhyx_photo," +
                "jyplmhxtwbcwhyx_photo,ygbjwfwhyx_photo,use_electric_bicycle_msg,use_bottled_liquefied_gas_msg,use_stacked_items_msg,mhqwbcwhyx_msg," +
                "jyplmhxtwbcwhyx_msg,ygbjwfwhyx_msg,use_electric_bicycle_zgqk,use_bottled_liquefied_gas_zgqk,use_stacked_items_zgqk,mhqwbcwhyx_zgqk," +
                "jyplmhxtwbcwhyx_zgqk,ygbjwfwhyx_msg_zgqk, use_electric_bicycle_zgphoto,use_bottled_liquefied_gas_zgphoto,use_stacked_items_zgphoto," +
                "mhqwbcwhyx_zgphoto,jyplmhxtwbcwhyx_zgphoto,ygbjwfwhyx_msg_zgphoto,slljdx,slljdx_photo, slljdx_msg,slljdx_zgqk,slljdx_zgphoto,zxqlddcsl," +
                "zxqlyhsyqsl,sign,notes from zm_pc_baselog t1, zm_pc_yhlog t2 where t1.id = t2.base_id ) b1 left join (select fwbh,base_id,COALESCE(yhnum,0) yhnum, " +
                "COALESCE(zgnum,0) zgnum from  zm_pc_yhlog t1 ,  (select count(fwbh) yhnum,yhlog_id,sum(case when zg_value='1' then 1 else 0 end) zgnum " +
                "from zm_pc_yhlog_dx  group by yhlog_id) t2 where t1.id=t2.yhlog_id) b2  on b1.id=b2.base_id) t  where id ='" + id + "'";
            var item = _dbContext.Database.GetDbConnection().Query<BuildingPcYhLogDetailDto>(sql).ToList();
            model.BuildingPcYhLogDetailDtoList = item;
            ///整改
            string sql_rectification = @"  select *from (select m1.*,check_id,check_name,zg_zp,zg_time,zg_by zg_name   from 
                    (select t1.base_id,t1.fwbh,t2.id dx_id from zm_pc_yhlog t1 left join zm_pc_yhlog_dx t2 on t1.id=t2.yhlog_id ) m1 ,
                    zm_pc_yhlog_dxzgson m2  where m1.dx_id=m2.yhlog_dx_id ) t where base_id='" + id + "'";

            var itemLIst = _dbContext.Database.GetDbConnection().Query<RectificationYh>(sql_rectification).ToList();
            model.RectificationYhList = itemLIst;
            //如果从自查里面没有找到，从检查里面找
            if (item == null||item.Count==0)
            {
                 model = new HourseBaseInfo();
                 sql = @$" select * from " +
                    "( select b1.*,yhnum from (select case when  t2.pczt ='1' then '无隐患' when t2.pczt ='2' then '有隐患' end pczt,t1.id,t1.fwbh,t1.yhly,t1.xzqdm,t1.town,t1.village,address,yhfwbh,hzxm,jzrks,zzrs,cs,fjsl,jzfjs,jzscxyaqcksl," +
                    "jzxyssltsl,jzscmj,ckfhwggptzlsl,bdfwjzrydddzxcsl,mhqsl,fytsmz,xggdjsbp,jzsyjyplmhxts,jzszlwxdlgybjqs,wdwhjgfjgqsyyrkrgbdj,sstdsyyrkrclzx," +
                    "gryssdcsszblyyqksddp,case when cjzwqtjyyt =0 then '否' else cjzwqtjyytmsg end cjzwqtjyytmsg,case when cs=1 and jzscxyaqcksl>=2 then '已达标'  else case when cs>=2 and jzscxyaqcksl>=2  and jzxyssltsl >=2 then '已达标' else sstdjsqk end  end jsrysstd,sstdjsqk,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') pcsj,cjzwqtjyyt,use_electric_bicycle,use_bottled_liquefied_gas,use_stacked_items," +
                    "mhqwbcwhyx,jyplmhxtwbcwhyx,ygbjwfwhyx,use_electric_bicycle_photo,use_bottled_liquefied_gas_photo,use_stacked_items_photo,mhqwbcwhyx_photo," +
                    "jyplmhxtwbcwhyx_photo,ygbjwfwhyx_photo,use_electric_bicycle_msg,use_bottled_liquefied_gas_msg,use_stacked_items_msg,mhqwbcwhyx_msg," +
                    "jyplmhxtwbcwhyx_msg,ygbjwfwhyx_msg,use_electric_bicycle_zgqk,use_bottled_liquefied_gas_zgqk,use_stacked_items_zgqk,mhqwbcwhyx_zgqk," +
                    "jyplmhxtwbcwhyx_zgqk,ygbjwfwhyx_msg_zgqk, use_electric_bicycle_zgphoto,use_bottled_liquefied_gas_zgphoto,use_stacked_items_zgphoto," +
                    "mhqwbcwhyx_zgphoto,jyplmhxtwbcwhyx_zgphoto,ygbjwfwhyx_msg_zgphoto,slljdx,slljdx_photo, slljdx_msg,slljdx_zgqk,slljdx_zgphoto,zxqlddcsl," +
                    "zxqlyhsyqsl,sign,notes from zm_pc_baselog_cj t1, zm_pc_yhlog_cj t2 where t1.id = t2.base_id ) b1 left join (select fwbh,base_id,COALESCE(yhnum,0) yhnum, " +
                    "COALESCE(zgnum,0) zgnum from  zm_pc_yhlog_cj t1 ,  (select count(fwbh) yhnum,yhlog_id,sum(case when zg_value='1' then 1 else 0 end) zgnum " +
                    "from zm_pc_yhlog_dx_cj  group by yhlog_id) t2 where t1.id=t2.yhlog_id) b2  on b1.id=b2.base_id) t  where id ='"+id+"' ";
                item = _dbContext.Database.GetDbConnection().Query<BuildingPcYhLogDetailDto>(sql).ToList();
                model.BuildingPcYhLogDetailDtoList = item;
                ///整改
                 sql_rectification = @" select *from (select m1.*,check_id,check_name,zg_zp,zg_time,zg_by zg_name   from 
                    (select t1.base_id,t1.fwbh,t2.id dx_id from zm_pc_yhlog_cj t1 left join zm_pc_yhlog_dx_cj t2 on t1.id=t2.yhlog_id ) m1 ,
                    zm_pc_yhlog_dxzgson_cj m2  where m1.dx_id=m2.yhlog_dx_id ) t where base_id='"+ id + "'";

                itemLIst = _dbContext.Database.GetDbConnection().Query<RectificationYh>(sql_rectification).ToList();
                model.RectificationYhList = itemLIst;
                //从核查里面找
                if (item == null || item.Count == 0)
                {
                    model = new HourseBaseInfo();
                    sql = @$"  select * from " +
                    "( select b1.*,yhnum from (select case when  t2.pczt ='1' then '无隐患' when t2.pczt ='2' then '有隐患' end pczt, t1.id,t1.fwbh,t1.yhly,t1.xzqdm,t1.town,t1.village,address,yhfwbh,hzxm,jzrks,zzrs,cs,fjsl,jzfjs,jzscxyaqcksl," +
                   "jzxyssltsl,jzscmj,ckfhwggptzlsl,bdfwjzrydddzxcsl,mhqsl,fytsmz,xggdjsbp,jzsyjyplmhxts,jzszlwxdlgybjqs,wdwhjgfjgqsyyrkrgbdj,sstdsyyrkrclzx," +
                    "gryssdcsszblyyqksddp,case when cjzwqtjyyt =0 then '否' else cjzwqtjyytmsg end cjzwqtjyytmsg,case when cs=1 and jzscxyaqcksl>=2 then '已达标'  else case when cs>=2 and jzscxyaqcksl>=2  and jzxyssltsl >=2 then '已达标' else sstdjsqk end  end jsrysstd,sstdjsqk,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') pcsj,cjzwqtjyyt,use_electric_bicycle,use_bottled_liquefied_gas,use_stacked_items," +
                   "mhqwbcwhyx,jyplmhxtwbcwhyx,ygbjwfwhyx,use_electric_bicycle_photo,use_bottled_liquefied_gas_photo,use_stacked_items_photo,mhqwbcwhyx_photo," +
                   "jyplmhxtwbcwhyx_photo,ygbjwfwhyx_photo,use_electric_bicycle_msg,use_bottled_liquefied_gas_msg,use_stacked_items_msg,mhqwbcwhyx_msg," +
                    "jyplmhxtwbcwhyx_msg,ygbjwfwhyx_msg,use_electric_bicycle_zgqk,use_bottled_liquefied_gas_zgqk,use_stacked_items_zgqk,mhqwbcwhyx_zgqk," +
                   "jyplmhxtwbcwhyx_zgqk,ygbjwfwhyx_msg_zgqk, use_electric_bicycle_zgphoto,use_bottled_liquefied_gas_zgphoto,use_stacked_items_zgphoto," +
                   "mhqwbcwhyx_zgphoto,jyplmhxtwbcwhyx_zgphoto,ygbjwfwhyx_msg_zgphoto,slljdx,slljdx_photo, slljdx_msg,slljdx_zgqk,slljdx_zgphoto,zxqlddcsl," +
                   "zxqlyhsyqsl,sign,notes from zm_pc_baselog_sqjz t1, zm_pc_yhlog_sqjz t2 where t1.id = t2.base_id ) b1 left join (select fwbh,base_id,COALESCE(yhnum,0) yhnum, " +
                  "COALESCE(zgnum,0) zgnum from  zm_pc_yhlog_sqjz t1 ,  (select count(fwbh) yhnum,yhlog_id,sum(case when zg_value='1' then 1 else 0 end) zgnum " +
                   "from zm_pc_yhlog_dx_sqjz  group by yhlog_id) t2 where t1.id=t2.yhlog_id) b2  on b1.id=b2.base_id) t  where id ='" + id + "' ";
                    item = _dbContext.Database.GetDbConnection().Query<BuildingPcYhLogDetailDto>(sql).ToList();
                    model.BuildingPcYhLogDetailDtoList = item;
                    ///整改
                    sql_rectification = @" select *from (select m1.*,check_id,check_name,zg_zp,zg_time,zg_by zg_name   from 
                    (select t1.base_id,t1.fwbh,t2.id dx_id from zm_pc_yhlog_sqjz t1 left join zm_pc_yhlog_dx_sqjz t2 on t1.id=t2.yhlog_id ) m1 ,
                    zm_pc_yhlog_dxzgson_sqjz m2  where m1.dx_id=m2.yhlog_dx_id ) t where base_id='"+ id +"' ";
                    itemLIst = _dbContext.Database.GetDbConnection().Query<RectificationYh>(sql_rectification).ToList();
                    model.RectificationYhList = itemLIst;
                }
            }
            return new RespRet<HourseBaseInfo>(true, "Ok", model);
        }




        /// <summary>
        /// 导出行政区、乡镇、村级工作进展数据
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("houses/evolvestatic/ExportExcel")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult ExportExcel([FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm)
        {
            try
            {
                //var list = new List<VillageFireSafetyAttrDto>();
                var userNumber = this.CurrentUser();

                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                var sbSql = new StringBuilder();
                var sbSqlWhere = new StringBuilder();
                var sqlParams = new List<NpgsqlParameter>();

                var sbSqlBuildingEx = new StringBuilder();

                string district = user.District;

                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


                string strWhere = "  ";

                if (user.RoleType == 1 || user.RoleType == 2)
                {
                    strWhere += " and xzq!='东城区' and xzq!='西城区' ";
                }
                else if (user.RoleType == 3)  //区级
                {
                    strWhere += " and xzqdm='" + model.RegionCode + "'";
                }
                else if (user.RoleType == 4)
                {
                    string tdm = model.RegionCode;
                    if (model.RegionCode.Length != 12)
                    {
                        tdm = model.RegionCode + "000";
                    }
                    strWhere += " and towndm='" + tdm + "'";
                }
                else if (user.RoleType == 5)
                {
                    strWhere += " and villagedm='" + model.RegionCode + "'";
                }


                if (!string.IsNullOrEmpty(xzqdm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "'";
                }
                if (!string.IsNullOrEmpty(towndm))
                {
                    strWhere += " and towndm='" + towndm + "'";
                }
                if (!string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and villagedm='" + villagedm + "'";
                }
                string today = DateTime.Now.ToString("yyyy-MM-dd");
                string table_fwzt = "";
                if (!string.IsNullOrEmpty(endTime))
                {
                    DateTime endData = Convert.ToDateTime(endTime);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                else
                {
                    DateTime endData = Convert.ToDateTime(today);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }
                string sql = "";
                if (!string.IsNullOrEmpty(xzqdm) && string.IsNullOrEmpty(towndm) && string.IsNullOrEmpty(villagedm))
                {
                    sql = @"select ROW_NUMBER() OVER ()index,* from zcqkxztext('" + startTime + "','" + endTime + " 23:59:59','" + xzqdm+"','" + table_fwzt + "') where 1 = 1 and xzq!= '东城区' and xzq!= '西城区'  " + strWhere;
                }
                else
                {
                   sql = @"select ROW_NUMBER() OVER ()index,* from zcqkcjtext('" + startTime + "','" + endTime + " 23:59:59','" + xzqdm+"','"+towndm+"','" + table_fwzt+"') where 1 = 1 and xzq!= '东城区' and xzq!= '西城区'  " + strWhere;
                }
                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet(model.RegionName+"");
                // 第一行 标题
                IRow row = sheet.CreateRow(0); 
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("乡镇/街名称");
                row.CreateCell(3).SetCellValue("村/社区名称");
                row.CreateCell(4).SetCellValue("房屋总数(栋)");
                row.CreateCell(5).SetCellValue("自查总数(栋)");
                row.CreateCell(6).SetCellValue("自查率");
                row.CreateCell(7).SetCellValue("隐患数量（个）");
                row.CreateCell(8).SetCellValue("整改数量（个）");
                row.CreateCell(9).SetCellValue("剩余隐患数量（个）");

                // 第二行 写数据
                int i = 1;
                    foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            var val = kvp.Value.ToString();
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if(kvp.Key == "village")
                            {
                                row.CreateCell(3).SetCellValue(val);
                            }
                            else if (kvp.Key == "town")
                            {
                                row.CreateCell(2).SetCellValue(val);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(val);
                            }
                            else if (kvp.Key == "zcount")
                            {
                                row.CreateCell(4).SetCellValue(val);
                            }
                            else if (kvp.Key == "zccount")
                            {
                                row.CreateCell(5).SetCellValue(val);
                            }
                            else if (kvp.Key == "yhcount")
                            {
                                row.CreateCell(7).SetCellValue(val);
                            }
                            else if (kvp.Key == "zgcount")
                            {
                                row.CreateCell(8).SetCellValue(val);
                            }
                            else if (kvp.Key == "zcl")
                            {
                                row.CreateCell(6).SetCellValue(val);
                            }
                            else if (kvp.Key == "syyhcount")
                            {
                                row.CreateCell(9).SetCellValue(val);
                            }
                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "村民经营性自建房消防安全排查治理村級工作进展统计.xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }

        }


        /// <summary>
        /// 导出村庄数据
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("houses/evolvestatic/ExportDetailedExcel")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult ExportDetailedExcel([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string startTime, [FromQuery] string endTime)
        {
            try
            {
                //var list = new List<VillageFireSafetyAttrDto>();
                var userNumber = this.CurrentUser();

                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                var sbSql = new StringBuilder();
                var sbSqlWhere = new StringBuilder();
                var sqlParams = new List<NpgsqlParameter>();

                var sbSqlBuildingEx = new StringBuilder();

                string district = user.District;

                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


                string strWhere = "  ";

                if (user.RoleType == 1 || user.RoleType == 2)
                {
                    strWhere += " and xzq!='东城区' and xzq!='西城区' ";
                   
                }
                else if (user.RoleType == 3)  //区级
                {
                    strWhere += " and xzqdm='" + model.RegionCode + "'";
                }
                else if (user.RoleType == 4)
                {
                    string tdm = model.RegionCode;
                    if (model.RegionCode.Length != 12)
                    {
                        tdm = model.RegionCode + "000";
                    }
                    strWhere += " and towndm='" + tdm + "'";
                }
                else if (user.RoleType == 5)
                {
                    strWhere += " and villagedm='" + model.RegionCode + "'";
                }

                if (!string.IsNullOrEmpty(xzqdm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "'";
                }
                if (!string.IsNullOrEmpty(towndm))
                {
                    strWhere += " and towndm='" + towndm + "'";
                }
                if (!string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and villagedm='" + villagedm + "'";
                }

                string today = DateTime.Now.ToString("yyyy-MM-dd");
                string table_fwzt = "";
                if (!string.IsNullOrEmpty(endTime))
                {
                    DateTime endData = Convert.ToDateTime(endTime);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                    if (endData > DateTime.Now)
                    {
                        table_fwzt = "zm_building_fwzt_" + endData.Year;
                    }
                }
                else
                {
                    DateTime endData = Convert.ToDateTime(today);
                    table_fwzt = "zm_building_fwzt_" + endData.Year;
                }


                string sql = @"select ROW_NUMBER() OVER ()index ,* from (select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,t1.hzxm,t1.address,t1.fwbh,case when  t2.create_on is null then '未自查' else '已自查' end zczt from 
                            ( select * from zm_buildings where fwbh not in (select fwbh from  "+ table_fwzt + " where final_fwzt is not null and final_create_on='"+endTime+" 23:59:59')) t1 left join (select xzqdm,towndm,villagedm,fwbh,create_on from zm_pc_yh where date(create_on) BETWEEN '" + startTime+"' and  '"+endTime+"') t2 on t1.fwbh=t2.fwbh) t where 1=1  "+ strWhere + "  order by xzqdm,towndm asc,villagedm asc";

                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet(model.RegionName + "");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("乡镇/街名称");
                row.CreateCell(3).SetCellValue("村/社区名称");
                row.CreateCell(4).SetCellValue("房主姓名");
                row.CreateCell(5).SetCellValue("地址");
                row.CreateCell(6).SetCellValue("房屋编号");
                row.CreateCell(7).SetCellValue("状态");
                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            var val = kvp.Value+"";
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "village")
                            {
                                row.CreateCell(3).SetCellValue(val);
                            }
                            else if (kvp.Key == "town")
                            {
                                row.CreateCell(2).SetCellValue(val);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(val);
                            }
                            else if (kvp.Key == "hzxm")
                            {
                                row.CreateCell(4).SetCellValue(val);
                            }
                            else if (kvp.Key == "address")
                            {
                                row.CreateCell(5).SetCellValue(val);
                            }
                            else if (kvp.Key == "fwbh")
                            {
                                row.CreateCell(6).SetCellValue(val);
                            }
                            else if (kvp.Key == "zczt")
                            {
                                row.CreateCell(7).SetCellValue(val);
                            }

                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "村民经营性自建房消防安全排查治理村級工作进展统计（自查明细）.xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }

        }

        /// <summary>
        /// 某栋房屋的检查、自查记录
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet("houses/baseInfo/baseInfoDetaile")]
        public RespRet<Page<object>> baseInfoDetaile([FromQuery] string fwbh, [FromQuery] int pageIndex, [FromQuery] int limit)
        {
            if (pageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            //var userNumber = this.CurrentUser();
            //if (string.IsNullOrEmpty(userNumber))
            //{
            //    return new RespRet<Page<object>>(false, "user not logined");
            //}
            //var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            //if (null == user)
            //{
            //    return new RespRet<Page<object>>(false, "用户名不存在");
            //}
            //string district = user.District;
            //var options = new JsonSerializerOptions();
            //options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            //Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string strWhere = "";

            string sql = @"select  ROW_NUMBER() OVER ()index ,*   from ( select b1.*,COALESCE(yhnum,0) yhnum,case when yhnum is null then '已自查无隐患' else  '已自查有隐患' end yhzt,COALESCE(zgnum,0) zgnum,
                         case when zgnum is null then '/' else case when zgnum=0 then '未整改' else  '已整改' end end zgzt,'详情' cz  from (select t2.hzxm,t2.address,t1.fwbh,t1.pc_yh_id,t1.base_id,t1.base_id id,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') pcsj  
                           from zm_pc_yhlog t1,zm_buildings t2 where t1.fwbh=t2.fwbh ) b1 left join 
                           (select count(fwbh) yhnum, yhlog_id, sum(case when zg_value= '1' then 1 else 0 end) zgnum from zm_pc_yhlog_dx 
                           group by yhlog_id) b2 on b1.id = b2.yhlog_id  
							 UNION					 
							select b1.*,COALESCE(yhnum,0) yhnum,case when yhnum is null then '已自查无隐患' else  '已自查有隐患' end yhzt,COALESCE(zgnum,0) zgnum,
                         case when zgnum is null then '/' else case when zgnum=0 then '未整改' else  '已整改' end end zgzt,'详情' cz  from (select t2.hzxm,t2.address,t1.fwbh,t1.pc_yh_id,t1.base_id,t1.base_id id,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') pcsj  
                           from zm_pc_yhlog_jblog t1,zm_buildings t2 where t1.fwbh=t2.fwbh ) b1 left join 
                           (select count(fwbh) yhnum, yhlog_id, sum(case when zg_value= '1' then 1 else 0 end) zgnum from zm_pc_yhlog_dx_jblog 
                           group by yhlog_id) b2 on b1.id = b2.yhlog_id   order by pcsj desc)  t   
                       where fwbh='" + fwbh + "' " + strWhere + "   limit " + limit + " offset " + limit * pageIndex;


            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)
         || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)
         || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            string sql_count = @"select  count(1) cnt  from ( select b1.*,COALESCE(yhnum,0) yhnum,case when yhnum is null then '已自查无隐患' else  '已自查有隐患' end yhzt,COALESCE(zgnum,0) zgnum,
                         case when zgnum is null then '/' else case when zgnum=0 then '未整改' else  '已整改' end end zgzt,'详情' cz  from (select t2.hzxm,t2.address,t1.fwbh,t1.pc_yh_id,t1.base_id,t1.base_id id,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') pcsj  
                           from zm_pc_yhlog t1,zm_buildings t2 where t1.fwbh=t2.fwbh ) b1 left join 
                           (select count(fwbh) yhnum, yhlog_id, sum(case when zg_value= '1' then 1 else 0 end) zgnum from zm_pc_yhlog_dx 
                           group by yhlog_id) b2 on b1.id = b2.yhlog_id  
							 UNION					 
							select b1.*,COALESCE(yhnum,0) yhnum,case when yhnum is null then '已自查无隐患' else  '已自查有隐患' end yhzt,COALESCE(zgnum,0) zgnum,
                         case when zgnum is null then '/' else case when zgnum=0 then '未整改' else  '已整改' end end zgzt,'详情' cz  from (select t2.hzxm,t2.address,t1.fwbh,t1.pc_yh_id,t1.base_id,t1.base_id id,to_char(t1.create_on::timestamp(0) without time zone, 'YYYY-MM-DD HH24:MI:SS') pcsj  
                           from zm_pc_yhlog_jblog t1,zm_buildings t2 where t1.fwbh=t2.fwbh ) b1 left join 
                           (select count(fwbh) yhnum, yhlog_id, sum(case when zg_value= '1' then 1 else 0 end) zgnum from zm_pc_yhlog_dx_jblog 
                           group by yhlog_id) b2 on b1.id = b2.yhlog_id   order by pcsj desc)  t    where fwbh='" + fwbh + "'" + strWhere;

            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
          || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
          || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            //执行语句
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, NextPageIndex = pageIndex, PageTotal = count, Total = count });

        }


        /// <summary>
        /// 分类导出明细
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("houses/evolvestatic/ExportHouseDetailedExcel")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult ExportHouseDetailedExcel([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string startTime, [FromQuery] string endTime, [FromQuery] string category)
        {
            try
            {
                //var list = new List<VillageFireSafetyAttrDto>();
                var userNumber = this.CurrentUser();

                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                var sbSql = new StringBuilder();
                var sbSqlWhere = new StringBuilder();
                var sqlParams = new List<NpgsqlParameter>();

                var sbSqlBuildingEx = new StringBuilder();

                string district = user.District;

                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


                string strWhere = " 1=1  ";

                if (user.RoleType == 1 || user.RoleType == 2)
                {
                    strWhere += " and xzq!='东城区' and xzq!='西城区' ";
                }
                else if (user.RoleType == 3)  //区级
                {
                    strWhere += " and xzqdm='" + model.RegionCode + "'";
                }
                else if (user.RoleType == 4)
                {
                    string tdm = model.RegionCode;
                    if (model.RegionCode.Length != 12)
                    {
                        tdm = model.RegionCode + "000";
                    }
                    strWhere += " and towndm='" + tdm + "'";
                }
                else if (user.RoleType == 5)
                {
                    strWhere += " and villagedm='" + model.RegionCode + "'";
                }

                if (!string.IsNullOrEmpty(xzqdm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "'";
                }
                if (!string.IsNullOrEmpty(towndm))
                {
                    strWhere += " and towndm='" + towndm + "'";
                }
                if (!string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and villagedm='" + villagedm + "'";
                }

                string sql = "";
                string filename = "";

                if (category == "1")
                {
                    filename = startTime+"-"+endTime+"已自查明细";
                    strWhere += " and date(create_on) between '" + startTime + "' and '" + endTime + "' " ;
                    sql = @" select ROW_NUMBER() OVER ()index,* from (select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,t2.hzxm,t2.address,t1.fwbh,t1.create_on from zm_pc_yhlog t1,(select *from (select t1.* ,t2.fwzt from zm_buildings t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh ) zm_buildings  where fwzt is   null) t2  where 1 = 1 and t1.fwbh=t2.fwbh) t where " + strWhere;
                }
                else if (category == "2")
                {
                    filename = startTime + "-" + endTime + "未自查明细";
                    sql = @" select ROW_NUMBER() OVER ()index, t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,t1.hzxm,t1.address,t1.fwbh from (select *from (select t1.* ,t2.fwzt from zm_buildings t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh ) zm_buildings  where fwzt is   null) t1  where   " + strWhere  + " and fwbh not in(select fwbh from zm_pc_yhlog where " + strWhere + "  and date(create_on) between '" + startTime + "' and '" + endTime + "')" ;
                }
                else if (category == "3")
                {
                    filename = startTime + "-" + endTime + "发现隐患情况";
                    strWhere += " and date(create_on) between '" + startTime + "' and '" + endTime + "' " ;
                    sql = @"select ROW_NUMBER() OVER ()index,* from (select distinct * from (select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,t2.hzxm,t2.address,t1.fwbh,t1.zg_value,t1.create_on from zm_pc_yhlog_dx t1,( select *from (select t1.* ,t2.fwzt from zm_buildings t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh ) zm_buildings  where fwzt is   null) t2  where 1 = 1 and  t1.fwbh=t2.fwbh) t) t where " + strWhere ;
                }
                else if (category == "4")
                {
                    filename = startTime + "-" + endTime + "已整改隐患情况";
                    strWhere += " and date(create_on) between '" + startTime + "' and '" + endTime + "' " ;
                    sql = @"select ROW_NUMBER() OVER ()index,* from (select distinct * from (select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,t2.hzxm,t2.address,t1.fwbh,t1.zg_value,t1.create_on from zm_pc_yhlog_dx t1,( select *from (select t1.* ,t2.fwzt from zm_buildings t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh ) zm_buildings  where fwzt is   null) t2  where 1 = 1 and  t1.fwbh=t2.fwbh) t where zg_value='1') t where " + strWhere ;
                }
                else if (category == "5")
                {
                    filename = startTime + "-" + endTime + "未整改隐患情况";
                    strWhere += " and date(create_on) between '" + startTime + "' and '" + endTime + "' " ;
                    sql = @"select ROW_NUMBER() OVER ()index,* from (select distinct * from (select t1.xzqdm,t1.xzq,t1.town,t1.towndm,t1.village,t1.villagedm,t2.hzxm,t2.address,t1.fwbh,t1.zg_value,t1.create_on from zm_pc_yhlog_dx t1,( select *from(select t1.* ,t2.fwzt from zm_buildings t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh ) zm_buildings  where fwzt is   null) t2  where 1 = 1 and t1.fwbh=t2.fwbh and zg_value='0' ) t) t where " + strWhere ;
                }

                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet(model.RegionName + "");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("乡镇/街名称");
                row.CreateCell(3).SetCellValue("村/社区名称");
                row.CreateCell(4).SetCellValue("房主姓名");
                row.CreateCell(5).SetCellValue("地址");
                row.CreateCell(6).SetCellValue("房屋编号");

                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            var val = kvp.Value + "";
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "village")
                            {
                                row.CreateCell(3).SetCellValue(val);
                            }
                            else if (kvp.Key == "town")
                            {
                                row.CreateCell(2).SetCellValue(val);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(val);
                            }
                            else if (kvp.Key == "hzxm")
                            {
                                row.CreateCell(4).SetCellValue(val);
                            }
                            else if (kvp.Key == "address")
                            {
                                row.CreateCell(5).SetCellValue(val);
                            }
                            else if (kvp.Key == "fwbh")
                            {
                                row.CreateCell(6).SetCellValue(val);
                            }

                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "村民经营性自建房消防安全排查治理"+ filename + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }

        }
        /// <summary>
        /// 获取当前房屋的是否有隐患
        /// </summary>
        /// <param name="id">记录ID</param>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("houses/isExistYh")]
        [Produces("application/json")]
        [ProducesResponseType(typeof(RespRet<object>), 200)]
        public RespRet<object> getIsExistYh([FromRoute] string fwbh)
        {
            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<object>(false, "user not logined");
            }

            string sql = "  select * from zm_pc_yhlog_dx_cj  where fwbh ='" + fwbh + "' and zg_value=0";
            var item = _dbContext.Database.GetDbConnection().Query<object>(sql).FirstOrDefault();
            if (item == null) 
            {
                sql = "  select * from zm_pc_yhlog_dx  where fwbh ='" + fwbh + "' and zg_value=0";
                item = _dbContext.Database.GetDbConnection().Query<object>(sql).FirstOrDefault();
            }

            return new RespRet<object>(true, "Ok", item);
        }
        private static string LogPath
        {
            get
            {
                string OutPath = "D:/xfservice/Log/";

                if (!Directory.Exists(OutPath))
                {
                    Directory.CreateDirectory(OutPath);
                }

                return OutPath + DateTime.Today.ToString("yyyy-MM-dd") + ".txt";
            }
        }

        public static bool Write(string needWriteInfo)
        {
            bool success = false;
            if (System.IO.File.Exists(LogPath))
            {
                //追加文件
                using (System.IO.FileStream fs = new System.IO.FileStream(LogPath, FileMode.Append, System.IO.FileAccess.Write, FileShare.ReadWrite))
                {
                    using (StreamWriter sw = new StreamWriter(fs, Encoding.UTF8))
                    {
                        //开始写入
                        sw.WriteLine(DateTime.Now + "： " + needWriteInfo + "\n");
                        success = true;
                    }
                }
            }
            else
            {
                using (System.IO.FileStream fs = new System.IO.FileStream(LogPath, FileMode.CreateNew, System.IO.FileAccess.ReadWrite, FileShare.ReadWrite))
                {
                    using (StreamWriter sw = new StreamWriter(fs, Encoding.UTF8))
                    {
                        //开始写入
                        sw.WriteLine(DateTime.Now + "： " + needWriteInfo + "\n");
                        success = true;
                    }
                }
            }

            return success;
        }

        [AllowAnonymous]
        [HttpGet("hourse/dynamic/removefwbh")]
        public Object removefwbh([FromQuery] string villagedmlist)
        {
            int count = 0;
            try
            {
                if (!string.IsNullOrEmpty(villagedmlist))
                {



                    string[] arrlist = villagedmlist.Split('|');

                    Write("涉及村数量：" + arrlist.Length);
                    if (arrlist.Length > 0)
                    {
                        for (int i = 0; i < arrlist.Length; i++)
                        {
                            string strvillagedm = arrlist[i] + "";

                            string sql = " select * from zm_buildings where villagedm='" + strvillagedm + "'";
                            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                            Write("第一个村房屋数量：" + list.Count);
                            if (list.Count > 0)
                            {
                                foreach (object obj in list)
                                {
                                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                                    if (obj != null)
                                    {
                                        string fwbh = "";
                                        string xzqdm = "";
                                        string town = "";
                                        string village = "";
                                        string address = "";
                                        string hzxm = "";
                                        string create_by = "admin";
                                        string xzq = "";
                                        string towndm = "";
                                        string villagedm = "";
                                        var properties = obj as IDictionary<string, object>;
                                        string id = Guid.NewGuid().ToString();
                                        string yh_id = Guid.NewGuid().ToString();
                                        foreach (var kvp in properties)
                                        {

                                            if (kvp.Key == "fwbh")
                                            {
                                                fwbh = kvp.Value + "";
                                            }
                                            if (kvp.Key == "xzqdm")
                                            {
                                                xzqdm = kvp.Value + "";
                                            }
                                            if (kvp.Key == "town")
                                            {
                                                town = kvp.Value + "";
                                            }
                                            if (kvp.Key == "village")
                                            {
                                                village = kvp.Value + "";
                                            }
                                            if (kvp.Key == "address")
                                            {
                                                address = kvp.Value + "";
                                            }
                                            if (kvp.Key == "hzxm")
                                            {
                                                hzxm = kvp.Value + "";
                                            }
                                            if (kvp.Key == "xzq")
                                            {
                                                xzq = kvp.Value + "";
                                            }
                                            if (kvp.Key == "towndm")
                                            {
                                                towndm = kvp.Value + "";
                                            }
                                            if (kvp.Key == "villagedm")
                                            {
                                                villagedm = kvp.Value + "";
                                            }
                                        }


                                        string c = "delete from zm_pc_yh_cj where fwbh='" + fwbh + "' and villagedm='" + villagedm + "';   insert into zm_pc_yh_cj(id,fwbh,pczt,jcly,create_by,pcsj,base_id,xzqdm,towndm,villagedm,xzq,town,village,create_on)" +
                                                "values('" + yh_id + "','" + fwbh + "','1','5','" + create_by + "','"
                                                + DateTime.Now + "','" + id + "','" + xzqdm + "','" + towndm + "','" + villagedm + "','" + xzq + "','" + town + "','" + village + "','" + DateTime.Now + "')";
                                        var cc = _dbContext.Database.GetDbConnection().Query<object>(c).ToList();
                                        string d = "insert into zm_pc_yhlog_cj(id,fwbh,pczt,jcly,create_by,pcsj,base_id,xzqdm,towndm,villagedm,xzq,town,village,create_on)" +
                                                "values('" + yh_id + "','" + fwbh + "','1','5','" + create_by + "','"
                                                + DateTime.Now + "','" + id + "','" + xzqdm + "','" + towndm + "','" + villagedm + "','" + xzq + "','" + town + "','" + village + "','" + DateTime.Now + "')";
                                        var dd = _dbContext.Database.GetDbConnection().Query<object>(d).ToList();

                                      // Write("添加检查记录：" + dd.Count+"************"+ cc.Count);
                                      //  Write("添加检查记录：" + c + "\n************" + d);

                                        string a = "delete from zm_pc_base_cj where fwbh='" + fwbh + "' and village='" + villagedm + "';  insert into zm_pc_base_cj(id,fwbh,yhly,xzqdm,town,village,address,hzxm,create_by,fwzt,pcsj,xzq,towndm,villagedm,create_name,create_on)" +
                                                "values('" + id + "','" + fwbh + "','5','" + xzqdm + "','" + towndm + "','"
                                                + villagedm + "','" + address + "','" + hzxm + "','" + create_by + "','2','" + DateTime.Now + "','" + xzq + "','" + town + "','" + village + "','" + create_by + "','" + DateTime.Now + "')";
                                        var aa = _dbContext.Database.GetDbConnection().Query<object>(a).ToList();

                                        string b = " insert into zm_pc_baselog_cj(id,fwbh,yhly,xzqdm,town,village,address,hzxm,create_by,fwzt,pcsj,xzq,towndm,villagedm,create_name,create_on)" +
                                                 "values('" + id + "','" + fwbh + "','5','" + xzqdm + "','" + towndm + "','"
                                                 + villagedm + "','" + address + "','" + hzxm + "','" + create_by + "','2','" + DateTime.Now + "','" + xzq + "','" + town + "','" + village + "','" + create_by + "','" + DateTime.Now + "')";

                                        var bb = _dbContext.Database.GetDbConnection().Query<object>(b).ToList();

                                    //    Write("添加房屋记录：" + aa.Count + "************" + bb.Count);
                                     //   Write("添加房屋记录：" + a + "\n************" + b);

                                        string e = " update zm_building_ledger set  iscy='是' where villagedm='" + strvillagedm + "' ";
                                        var ee = _dbContext.Database.GetDbConnection().Query<object>(e).ToList();

                                        count++;
                                    }
                                }


                            }




                        }
                    }
                }

            } 
            catch (Exception ex)
            {

                throw ex;
            }
          

            return new RespRet(true, "共删除：" + count + "条");

        }



        /// <summary>
        /// 某栋房屋的检查、自查记录
        /// </summary>
        /// <param name="pageRequest"></param>
        /// <returns></returns>
        [HttpGet("houses/stat/sysstdlist")]
        public RespRet<Page<object>> baseInfoDetaile( [FromQuery] int pageIndex, [FromQuery] int limit, [FromQuery] string startTime, [FromQuery] string endTime)
        {
            if (pageIndex < 0)
            {
                return new RespRet<Page<object>>(false, "PageIndex must >= 0");
            }

            var userNumber = this.CurrentUser();
            if (string.IsNullOrEmpty(userNumber))
            {
                return new RespRet<Page<object>>(false, "user not logined");
            }
            var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
            if (null == user)
            {
                return new RespRet<Page<object>>(false, "用户名不存在");
            }
            string district = user.District;
            var options = new JsonSerializerOptions();
            options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
            Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);

            string strWhere = "";
            if (model != null)
            {
                if (user.RoleType == 1 || user.RoleType == 2)
                {
                    strWhere += "and  xzq!='东城区' and xzq!='西城区'";
                }
                else if (user.RoleType == 3) //区级
                {
                    strWhere += " and xzqdm='" + model.RegionCode + "'";
                }
                else  //乡镇
                {
                    strWhere += "and xzqdm='" + model.RegionCode.Substring(0, 6) + "' ";
                }
            }
            if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
            {
                startTime = "2024-01-01";
                endTime = DateTime.Now.ToString("yyyy-MM-dd");
            }

            string sql = "select ROW_NUMBER() OVER ()index ,*  from (select mm.*,one,two,three,"
  +  "          case when                        "                                                                                                                                                                                                                                                                                                                           
  +  "              one_yccount=0                                                                                                                                                                                                                                                              "
  +  "           then '0.00%' else                                                                                                                                                                                                                                                             "
  +  "          concat(                                                                                                                                                                                                                                                                        "
  +  "                  CASE                                                                                                                                                                                                                                                                   "
  +  "                      WHEN (round((((one_yccount)::numeric / (one)::numeric) * (100)::numeric), 2) > (100)::numeric) THEN 100.00                                                                                                                                                         "
  +  "                      ELSE round((((one_yccount)::numeric / (one)::numeric) * (100)::numeric), 2)                                                                                                                                                                                        "
  +  "                  END, '%')                                                                                                                                                                                                                                                              "
  +  "                                                                                                                                                                                                                                                                                         "
	+"                end one_pencent,                                                                                                                                                                                                                                                         "
	+"                case when                                                                                                                                                                                                                                                                "
	+"                two_yccount=0                                                                                                                                                                                                                                                            "
  + "           then '0.00%' else                                                                                                                                                                                                                                                              "
  + "          concat(                                                                                                                                                                                                                                                                         "
  + "                  CASE                                                                                                                                                                                                                                                                    "
  + "                      WHEN (round((((two_yccount)::numeric / (two)::numeric) * (100)::numeric), 2) > (100)::numeric) THEN 100.00                                                                                                                                                          "
  + "                      ELSE round((((two_yccount)::numeric / (two)::numeric) * (100)::numeric), 2)                                                                                                                                                                                         "
  + "                  END, '%')                                                                                                                                                                                                                                                               "
  + "                                                                                                                                                                                                                                                                                          "
	+   "             end two_pencent,                                                                                                                                                                                                                                                         "
	+   "             case when                                                                                                                                                                                                                                                                "
	+   "             two_yccount=0                                                                                                                                                                                                                                                            "
  +     "        then '0.00%' else                                                                                                                                                                                                                                                             "
  +     "       concat(                                                                                                                                                                                                                                                                        "
  +     "               CASE                                                                                                                                                                                                                                                                   "
  +     "                   WHEN (round((((two_yccount)::numeric / (three)::numeric) * (100)::numeric), 2) > (100)::numeric) THEN 100.00                                                                                                                                                       "
  +     "                   ELSE round((((two_yccount)::numeric / (three)::numeric) * (100)::numeric), 2)                                                                                                                                                                                      "
  +     "               END, '%')                                                                                                                                                                                                                                                              "
  +     "                                                                                                                                                                                                                                                                                      "
	+   "             end three_pencent                                                                                                                                                                                                                                                        "
  +     "                                                                                                                                                                                                                                                                                      "
  +     "                                                                                                                                                                                                                                                                                      "
  +     "                                                                                                                                                                                                                                                                                      "
  +     "                                                                                                                                                                                                                                                                                      "
  +     "        from (select t7.*,three_ywccount from (select t5.*,two_yccount from (select t3.*,one_yccount from (select t1.xzqdm, t1.xzq, t2.zccount,t1.ssltdbfjsl,(zccount-ssltdbfjsl) wwccount,case when                                                                                  "
	+   "             ssltdbfjsl=0                                                                                                                                                                                                                                                             "
  +     "        then '0.00%' else                                                                                                                                                                                                                                                             "
  +     "       concat(                                                                                                                                                                                                                                                                        "
  +     "               CASE                                                                                                                                                                                                                                                                   "
  +     "                   WHEN (round((((ssltdbfjsl)::numeric / (zccount)::numeric) * (100)::numeric), 2) > (100)::numeric) THEN 100.00                                                                                                                                                      "
  +     "                   ELSE round((((ssltdbfjsl)::numeric / (zccount)::numeric) * (100)::numeric), 2)                                                                                                                                                                                     "
  +     "               END, '%')                                                                                                                                                                                                                                                              "
  +     "                                                                                                                                                                                                                                                                                      "
	+   "             end dbl_pencent                                                                                                                                                                                                                                                          "
	+   "                                                                                                                                                                                                                                                                                      "
	+   "                                                                                                                                                                                                                                                                                      "
	+   "                                                                                                                                                                                                                                                                                      "
	+   "              from (SELECT                                                                                                                                                                                                                                                            "
  +     "                   sum(                                                                                                                                                                                                                                                               "
  +     "                       CASE                                                                                                                                                                                                                                                           "
  +     "                           WHEN ((zm_pc_base.cs = 1) AND (zm_pc_base.jzscxyaqcksl >= 2)) THEN 1                                                                                                                                                                                       "
  + "                           WHEN ((zm_pc_base.cs >= 2) AND (zm_pc_base.jzscxyaqcksl >= 2) AND (zm_pc_base.jzxyssltsl >= 2)) THEN 1                                                                                                                                                   "
  +     "                           ELSE 0                                                                                                                                                                                                                                                     "
  +     "                       END) AS ssltdbfjsl,                                                                                                                                                                                                                                            "
  +     "                                                                                                                                                                                                                                                                                      "
  +     "                   zm_pc_base.xzq,                                                                                                                                                                                                                                                    "
  +     "                   zm_pc_base.xzqdm                                                                                                                                                                                                                                                   "
  + "                  FROM (select t1.*,t2.fwzt From zm_pc_yh t0  join zm_pc_base t1 on t0.fwbh=t1.fwbh LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh = t2.fwbh where date(t0.create_on) between '"+ startTime + "' and '"+endTime+"')zm_pc_base                                                      "
  +     "                 WHERE ((zm_pc_base.village)::text IN ( SELECT zm_buildings.villagedm                                                                                                                                                                                                 "
  +     "                          FROM zm_buildings))  and   date(create_on) between '"+ startTime + "' and '"+endTime+"' and fwzt is null                                                                                                                                                    "
  +     "                 GROUP BY zm_pc_base.xzqdm, zm_pc_base.xzq) t1                                                                                                                                                                                                                        "
	+	"			                                                                                                                                                                                                                                                                           "
	+	"			left join                                                                                                                                                                                                                                                                  "
	+	"			(                                                                                                                                                                                                                                                                          "
	+	"			SELECT zm_pc_yh.xzqdm,                                                                                                                                                                                                                                                     "
  +     "           zm_pc_yh.xzq,                                                                                                                                                                                                                                                              "
  +     "           count(distinct zm_pc_yh.fwbh) AS zccount                                                                                                                                                                                                                                   "
  +     "          FROM (select t1.*,t2.fwzt from zm_pc_yhlog t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh ) zm_pc_yh WHERE  date(create_on) between '"+ startTime + "' and '"+endTime+"' and fwzt is null                                                                                 "
  +     "         GROUP BY zm_pc_yh.xzqdm, zm_pc_yh.xzq                                                                                                                                                                                                                                        "
	+	"			)t2 on t1.xzqdm=t2.xzqdm) t3                                                                                                                                                                                                                                               "
	+	"			                                                                                                                                                                                                                                                                           "
	+	"			left join (                                                                                                                                                                                                                                                                "
	+	"			SELECT                                                                                                                                                                                                                                                                     "
  +     "   sum(                                                                                                                                                                                                                                                                               "
  +     "       CASE                                                                                                                                                                                                                                                                           "
  +     "           WHEN ((zm_pc_base.cs = 1) AND (zm_pc_base.jzscxyaqcksl >= 2)) THEN 1                                                                                                                                                                                                       "
  +     "           ELSE 0                                                                                                                                                                                                                                                                     "
  +     "       END) AS one_yccount,                                                                                                                                                                                                                                                           "
  +     "                                                                                                                                                                                                                                                                                      "
  +     "   zm_pc_base.xzq,                                                                                                                                                                                                                                                                    "
  +     "   zm_pc_base.xzqdm                                                                                                                                                                                                                                                                   "
  +     "  FROM (select t1.*,t2.fwzt From zm_pc_yh t0  join zm_pc_base t1 on t0.fwbh=t1.fwbh LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh = t2.fwbh where date(t0.create_on) between '"+ startTime + "' and '"+endTime+"')zm_pc_base                                                                  "
  +     " WHERE ((zm_pc_base.village)::text IN ( SELECT zm_buildings.villagedm                                                                                                                                                                                                                 "
  +     "          FROM zm_buildings))  and   date(create_on) between '"+ startTime + "' and '"+endTime+"' and fwzt is null and cs=1                                                                                                                                                           "
  +     " GROUP BY zm_pc_base.xzqdm, zm_pc_base.xzq                                                                                                                                                                                                                                            "
	+	"			) t4 on t3.xzqdm=t4.xzqdm) t5 left join (                                                                                                                                                                                                                                  "
	+	"			                                                                                                                                                                                                                                                                           "
	+	"				SELECT                                                                                                                                                                                                                                                                 "
  +     "   sum(                                                                                                                                                                                                                                                                               "
  +     "       CASE                                                                                                                                                                                                                                                                           "
  + "           WHEN ((zm_pc_base.cs = 2) AND (zm_pc_base.jzscxyaqcksl >= 2) AND (zm_pc_base.jzxyssltsl >= 2)) THEN 1                                                                                                                                                                    "
  +     "           ELSE 0                                                                                                                                                                                                                                                                     "
  +     "       END) AS two_yccount,                                                                                                                                                                                                                                                           "
  +     "                                                                                                                                                                                                                                                                                      "
  +     "   zm_pc_base.xzq,                                                                                                                                                                                                                                                                    "
  +     "   zm_pc_base.xzqdm                                                                                                                                                                                                                                                                   "
  +     "  FROM (select t1.*,t2.fwzt From zm_pc_yh t0  join zm_pc_base t1 on t0.fwbh=t1.fwbh LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh = t2.fwbh where date(t0.create_on) between '"+ startTime + "' and '"+endTime+"')zm_pc_base                                                                  "
  +     " WHERE ((zm_pc_base.village)::text IN ( SELECT zm_buildings.villagedm                                                                                                                                                                                                                 "
  +     "          FROM zm_buildings))  and   date(create_on) between '"+ startTime + "' and '"+endTime+"' and fwzt is null and cs=2                                                                                                                                                           "
  +     " GROUP BY zm_pc_base.xzqdm, zm_pc_base.xzq) t6 on t5.xzqdm=t6.xzqdm) t7 left join (                                                                                                                                                                                                   "
	+	"			                                                                                                                                                                                                                                                                           "
	+	"			                                                                                                                                                                                                                                                                           "
	+	"			                                                                                                                                                                                                                                                                           "
	+	"				SELECT                                                                                                                                                                                                                                                                 "
  +     "   sum(                                                                                                                                                                                                                                                                               "
  +     "       CASE                                                                                                                                                                                                                                                                           "
  + "           WHEN ((zm_pc_base.cs >=3) AND (zm_pc_base.jzscxyaqcksl >= 2) AND (zm_pc_base.jzxyssltsl >= 2)) THEN 1                                                                                                                                                                    "
  +     "           ELSE 0                                                                                                                                                                                                                                                                     "
  +     "       END) AS three_ywccount,                                                                                                                                                                                                                                                        "
  +     "                                                                                                                                                                                                                                                                                      "
  +     "   zm_pc_base.xzq,                                                                                                                                                                                                                                                                    "
  +     "   zm_pc_base.xzqdm                                                                                                                                                                                                                                                                   "
  +     "  FROM (select t1.*,t2.fwzt From zm_pc_yh t0  join zm_pc_base t1 on t0.fwbh=t1.fwbh LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh = t2.fwbh where date(t0.create_on) between '"+ startTime + "' and '"+endTime+"')zm_pc_base                                                                  "
  +     " WHERE ((zm_pc_base.village)::text IN ( SELECT zm_buildings.villagedm                                                                                                                                                                                                                 "
  +     "          FROM zm_buildings))  and   date(create_on) between '"+ startTime + "' and '"+endTime+"' and fwzt is null and cs>=3                                                                                                                                                          "
  +     " GROUP BY zm_pc_base.xzqdm, zm_pc_base.xzq) t8 on t7.xzqdm=t8.xzqdm) mm                                                                                                                                                                                                               "
	+	"			                                                                                                                                                                                                                                                                           "
	+	"			                                                                                                                                                                                                                                                                           "
	+	"			left join                                                                                                                                                                                                                                                                  "
	+	"			(                                                                                                                                                                                                                                                                          "
	+	"				SELECT                                                                                                                                                                                                                                                                 "
  +     "   count(1) AS three,                                                                                                                                                                                                                                                                 "
  +     "                                                                                                                                                                                                                                                                                      "
  +     "   zm_pc_base.xzq,                                                                                                                                                                                                                                                                    "
  +     "   zm_pc_base.xzqdm                                                                                                                                                                                                                                                                   "
  +     "  FROM (select t1.*,t2.fwzt From zm_pc_yh t0  join zm_pc_base t1 on t0.fwbh=t1.fwbh LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh = t2.fwbh where date(t0.create_on) between '"+ startTime + "' and '"+endTime+"')zm_pc_base                                                                  "
  +     " WHERE ((zm_pc_base.village)::text IN ( SELECT zm_buildings.villagedm                                                                                                                                                                                                                 "
  +     "          FROM zm_buildings))  and   date(create_on) between '"+ startTime + "' and '"+endTime+"' and fwzt is null and cs>=3                                                                                                                                                          "
  +     " GROUP BY zm_pc_base.xzqdm, zm_pc_base.xzq                                                                                                                                                                                                                                            "
	+	"			)nn on mm.xzqdm=nn.xzqdm                                                                                                                                                                                                                                                   "
	+	"			                                                                                                                                                                                                                                                                           "
	+	"			left join                                                                                                                                                                                                                                                                  "
	+	"			(                                                                                                                                                                                                                                                                          "
	+	"				SELECT                                                                                                                                                                                                                                                                 "
  +     "   count(1) AS two,                                                                                                                                                                                                                                                                   "
  +     "                                                                                                                                                                                                                                                                                      "
  +     "   zm_pc_base.xzq,                                                                                                                                                                                                                                                                    "
  +     "   zm_pc_base.xzqdm                                                                                                                                                                                                                                                                   "
  +     "  FROM (select t1.*,t2.fwzt From zm_pc_yh t0  join zm_pc_base t1 on t0.fwbh=t1.fwbh LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh = t2.fwbh where date(t0.create_on) between '"+ startTime + "' and '"+endTime+"')zm_pc_base                                                                  "
  +     " WHERE ((zm_pc_base.village)::text IN ( SELECT zm_buildings.villagedm                                                                                                                                                                                                                 "
  +     "          FROM zm_buildings))  and   date(create_on) between '"+ startTime + "' and '"+endTime+"' and fwzt is null and cs=2                                                                                                                                                           "
  +     " GROUP BY zm_pc_base.xzqdm, zm_pc_base.xzq                                                                                                                                                                                                                                            "
	+	"			)kk on mm.xzqdm=kk.xzqdm                                                                                                                                                                                                                                                   "
	+	"			left join                                                                                                                                                                                                                                                                  "
	+	"			(                                                                                                                                                                                                                                                                          "
	+	"				SELECT                                                                                                                                                                                                                                                                 "
  +     "   count(1) AS one,                                                                                                                                                                                                                                                                   "
  +     "                                                                                                                                                                                                                                                                                      "
  +     "   zm_pc_base.xzq,                                                                                                                                                                                                                                                                    "
  +     "   zm_pc_base.xzqdm                                                                                                                                                                                                                                                                   "
  +     "  FROM (select t1.*,t2.fwzt From zm_pc_yh t0  join zm_pc_base t1 on t0.fwbh=t1.fwbh LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh = t2.fwbh where date(t0.create_on) between '"+ startTime + "' and '"+endTime+"')zm_pc_base                                                                  "
  +     " WHERE ((zm_pc_base.village)::text IN ( SELECT zm_buildings.villagedm                                                                                                                                                                                                                 "
  +     "          FROM zm_buildings))  and   date(create_on) between '"+ startTime + "' and '"+endTime+"' and fwzt is null and cs=1                                                                                                                                                           "
  +     " GROUP BY zm_pc_base.xzqdm, zm_pc_base.xzq                                                                                                                                                                                                                                            "
	+	"			)hh on mm.xzqdm=hh.xzqdm                                                                                                                                                                                                                                                   "
	+"			) t  where " + strWhere + "   limit " + limit + " offset " + limit * pageIndex;                                                                                                                                                                                           
                                                                                                                                                                                                                                                                                           
                                                                                                                                                                                                                                                                                           
            if (sql.Contains("insert into", StringComparison.OrdinalIgnoreCase)                                                                                                                                                                                                            
         || sql.Contains("delete from", StringComparison.OrdinalIgnoreCase)                                                                                                                                                                                                                
         || sql.Contains("update ", StringComparison.OrdinalIgnoreCase))                                                                                                                                                                                                                   
            {                                                                                                                                                                                                                                                                              
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");                                                                                                                                                                                                           
            }
            string sql_count = "select count(1) cnt  from (select mm.*,one,two,three,"
  + "          case when                        "
  + "              one_yccount=0                                                                                                                                                                                                                                                              "
  + "           then '0.00%' else                                                                                                                                                                                                                                                             "
  + "          concat(                                                                                                                                                                                                                                                                        "
  + "                  CASE                                                                                                                                                                                                                                                                   "
  + "                      WHEN (round((((one_yccount)::numeric / (one)::numeric) * (100)::numeric), 2) > (100)::numeric) THEN 100.00                                                                                                                                                         "
  + "                      ELSE round((((one_yccount)::numeric / (one)::numeric) * (100)::numeric), 2)                                                                                                                                                                                        "
  + "                  END, '%')                                                                                                                                                                                                                                                              "
  + "                                                                                                                                                                                                                                                                                         "
    + "                end one_pencent,                                                                                                                                                                                                                                                         "
    + "                case when                                                                                                                                                                                                                                                                "
    + "                two_yccount=0                                                                                                                                                                                                                                                            "
  + "           then '0.00%' else                                                                                                                                                                                                                                                              "
  + "          concat(                                                                                                                                                                                                                                                                         "
  + "                  CASE                                                                                                                                                                                                                                                                    "
  + "                      WHEN (round((((two_yccount)::numeric / (two)::numeric) * (100)::numeric), 2) > (100)::numeric) THEN 100.00                                                                                                                                                          "
  + "                      ELSE round((((two_yccount)::numeric / (two)::numeric) * (100)::numeric), 2)                                                                                                                                                                                         "
  + "                  END, '%')                                                                                                                                                                                                                                                               "
  + "                                                                                                                                                                                                                                                                                          "
    + "             end two_pencent,                                                                                                                                                                                                                                                         "
    + "             case when                                                                                                                                                                                                                                                                "
    + "             two_yccount=0                                                                                                                                                                                                                                                            "
  + "        then '0.00%' else                                                                                                                                                                                                                                                             "
  + "       concat(                                                                                                                                                                                                                                                                        "
  + "               CASE                                                                                                                                                                                                                                                                   "
  + "                   WHEN (round((((two_yccount)::numeric / (three)::numeric) * (100)::numeric), 2) > (100)::numeric) THEN 100.00                                                                                                                                                       "
  + "                   ELSE round((((two_yccount)::numeric / (three)::numeric) * (100)::numeric), 2)                                                                                                                                                                                      "
  + "               END, '%')                                                                                                                                                                                                                                                              "
  + "                                                                                                                                                                                                                                                                                      "
    + "             end three_pencent                                                                                                                                                                                                                                                        "
  + "                                                                                                                                                                                                                                                                                      "
  + "                                                                                                                                                                                                                                                                                      "
  + "                                                                                                                                                                                                                                                                                      "
  + "                                                                                                                                                                                                                                                                                      "
  + "        from (select t7.*,three_ywccount from (select t5.*,two_yccount from (select t3.*,one_yccount from (select t1.xzqdm, t1.xzq, t2.zccount,t1.ssltdbfjsl,(zccount-ssltdbfjsl) wwccount,case when                                                                                  "
    + "             ssltdbfjsl=0                                                                                                                                                                                                                                                             "
  + "        then '0.00%' else                                                                                                                                                                                                                                                             "
  + "       concat(                                                                                                                                                                                                                                                                        "
  + "               CASE                                                                                                                                                                                                                                                                   "
  + "                   WHEN (round((((ssltdbfjsl)::numeric / (zccount)::numeric) * (100)::numeric), 2) > (100)::numeric) THEN 100.00                                                                                                                                                      "
  + "                   ELSE round((((ssltdbfjsl)::numeric / (zccount)::numeric) * (100)::numeric), 2)                                                                                                                                                                                     "
  + "               END, '%')                                                                                                                                                                                                                                                              "
  + "                                                                                                                                                                                                                                                                                      "
    + "             end dbl_pencent                                                                                                                                                                                                                                                          "
    + "                                                                                                                                                                                                                                                                                      "
    + "                                                                                                                                                                                                                                                                                      "
    + "                                                                                                                                                                                                                                                                                      "
    + "              from (SELECT                                                                                                                                                                                                                                                            "
  + "                   sum(                                                                                                                                                                                                                                                               "
  + "                       CASE                                                                                                                                                                                                                                                           "
  + "                           WHEN ((zm_pc_base.cs = 1) AND (zm_pc_base.jzscxyaqcksl >= 2)) THEN 1                                                                                                                                                                                       "
  + "                           WHEN ((zm_pc_base.cs >= 2) AND (zm_pc_base.jzscxyaqcksl >= 2) AND (zm_pc_base.jzscxyaqcksl >= 2)) THEN 1                                                                                                                                                   "
  + "                           ELSE 0                                                                                                                                                                                                                                                     "
  + "                       END) AS ssltdbfjsl,                                                                                                                                                                                                                                            "
  + "                                                                                                                                                                                                                                                                                      "
  + "                   zm_pc_base.xzq,                                                                                                                                                                                                                                                    "
  + "                   zm_pc_base.xzqdm                                                                                                                                                                                                                                                   "
  + "                  FROM (select t1.*,t2.fwzt From zm_pc_yh t0  join zm_pc_base t1 on t0.fwbh=t1.fwbh LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh = t2.fwbh where date(t0.create_on) between '" + startTime + "' and '" + endTime + "')zm_pc_base                                                      "
  + "                 WHERE ((zm_pc_base.village)::text IN ( SELECT zm_buildings.villagedm                                                                                                                                                                                                 "
  + "                          FROM zm_buildings))  and   date(create_on) between '" + startTime + "' and '" + endTime + "' and fwzt is null                                                                                                                                                    "
  + "                 GROUP BY zm_pc_base.xzqdm, zm_pc_base.xzq) t1                                                                                                                                                                                                                        "
    + "			                                                                                                                                                                                                                                                                           "
    + "			left join                                                                                                                                                                                                                                                                  "
    + "			(                                                                                                                                                                                                                                                                          "
    + "			SELECT zm_pc_yh.xzqdm,                                                                                                                                                                                                                                                     "
  + "           zm_pc_yh.xzq,                                                                                                                                                                                                                                                              "
  + "           count(distinct zm_pc_yh.fwbh) AS zccount                                                                                                                                                                                                                                   "
  + "          FROM (select t1.*,t2.fwzt from zm_pc_yhlog t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh ) zm_pc_yh WHERE  date(create_on) between '" + startTime + "' and '" + endTime + "' and fwzt is null                                                                                 "
  + "         GROUP BY zm_pc_yh.xzqdm, zm_pc_yh.xzq                                                                                                                                                                                                                                        "
    + "			)t2 on t1.xzqdm=t2.xzqdm) t3                                                                                                                                                                                                                                               "
    + "			                                                                                                                                                                                                                                                                           "
    + "			left join (                                                                                                                                                                                                                                                                "
    + "			SELECT                                                                                                                                                                                                                                                                     "
  + "   sum(                                                                                                                                                                                                                                                                               "
  + "       CASE                                                                                                                                                                                                                                                                           "
  + "           WHEN ((zm_pc_base.cs = 1) AND (zm_pc_base.jzscxyaqcksl >= 2)) THEN 1                                                                                                                                                                                                       "
  + "           ELSE 0                                                                                                                                                                                                                                                                     "
  + "       END) AS one_yccount,                                                                                                                                                                                                                                                           "
  + "                                                                                                                                                                                                                                                                                      "
  + "   zm_pc_base.xzq,                                                                                                                                                                                                                                                                    "
  + "   zm_pc_base.xzqdm                                                                                                                                                                                                                                                                   "
  + "  FROM (select t1.*,t2.fwzt From zm_pc_yh t0  join zm_pc_base t1 on t0.fwbh=t1.fwbh LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh = t2.fwbh where date(t0.create_on) between '" + startTime + "' and '" + endTime + "')zm_pc_base                                                                  "
  + " WHERE ((zm_pc_base.village)::text IN ( SELECT zm_buildings.villagedm                                                                                                                                                                                                                 "
  + "          FROM zm_buildings))  and   date(create_on) between '" + startTime + "' and '" + endTime + "' and fwzt is null and cs=1                                                                                                                                                           "
  + " GROUP BY zm_pc_base.xzqdm, zm_pc_base.xzq                                                                                                                                                                                                                                            "
    + "			) t4 on t3.xzqdm=t4.xzqdm) t5 left join (                                                                                                                                                                                                                                  "
    + "			                                                                                                                                                                                                                                                                           "
    + "				SELECT                                                                                                                                                                                                                                                                 "
  + "   sum(                                                                                                                                                                                                                                                                               "
  + "       CASE                                                                                                                                                                                                                                                                           "
  + "           WHEN ((zm_pc_base.cs = 2) AND (zm_pc_base.jzscxyaqcksl >= 2) AND (zm_pc_base.jzscxyaqcksl >= 2)) THEN 1                                                                                                                                                                    "
  + "           ELSE 0                                                                                                                                                                                                                                                                     "
  + "       END) AS two_yccount,                                                                                                                                                                                                                                                           "
  + "                                                                                                                                                                                                                                                                                      "
  + "   zm_pc_base.xzq,                                                                                                                                                                                                                                                                    "
  + "   zm_pc_base.xzqdm                                                                                                                                                                                                                                                                   "
  + "  FROM (select t1.*,t2.fwzt From zm_pc_yh t0  join zm_pc_base t1 on t0.fwbh=t1.fwbh LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh = t2.fwbh where date(t0.create_on) between '" + startTime + "' and '" + endTime + "')zm_pc_base                                                                  "
  + " WHERE ((zm_pc_base.village)::text IN ( SELECT zm_buildings.villagedm                                                                                                                                                                                                                 "
  + "          FROM zm_buildings))  and   date(create_on) between '" + startTime + "' and '" + endTime + "' and fwzt is null and cs=2                                                                                                                                                           "
  + " GROUP BY zm_pc_base.xzqdm, zm_pc_base.xzq) t6 on t5.xzqdm=t6.xzqdm) t7 left join (                                                                                                                                                                                                   "
    + "			                                                                                                                                                                                                                                                                           "
    + "			                                                                                                                                                                                                                                                                           "
    + "			                                                                                                                                                                                                                                                                           "
    + "				SELECT                                                                                                                                                                                                                                                                 "
  + "   sum(                                                                                                                                                                                                                                                                               "
  + "       CASE                                                                                                                                                                                                                                                                           "
  + "           WHEN ((zm_pc_base.cs >=3) AND (zm_pc_base.jzscxyaqcksl >= 2) AND (zm_pc_base.jzscxyaqcksl >= 2)) THEN 1                                                                                                                                                                    "
  + "           ELSE 0                                                                                                                                                                                                                                                                     "
  + "       END) AS three_ywccount,                                                                                                                                                                                                                                                        "
  + "                                                                                                                                                                                                                                                                                      "
  + "   zm_pc_base.xzq,                                                                                                                                                                                                                                                                    "
  + "   zm_pc_base.xzqdm                                                                                                                                                                                                                                                                   "
  + "  FROM (select t1.*,t2.fwzt From zm_pc_yh t0  join zm_pc_base t1 on t0.fwbh=t1.fwbh LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh = t2.fwbh where date(t0.create_on) between '" + startTime + "' and '" + endTime + "')zm_pc_base                                                                  "
  + " WHERE ((zm_pc_base.village)::text IN ( SELECT zm_buildings.villagedm                                                                                                                                                                                                                 "
  + "          FROM zm_buildings))  and   date(create_on) between '" + startTime + "' and '" + endTime + "' and fwzt is null and cs>=3                                                                                                                                                          "
  + " GROUP BY zm_pc_base.xzqdm, zm_pc_base.xzq) t8 on t7.xzqdm=t8.xzqdm) mm                                                                                                                                                                                                               "
    + "			                                                                                                                                                                                                                                                                           "
    + "			                                                                                                                                                                                                                                                                           "
    + "			left join                                                                                                                                                                                                                                                                  "
    + "			(                                                                                                                                                                                                                                                                          "
    + "				SELECT                                                                                                                                                                                                                                                                 "
  + "   count(1) AS three,                                                                                                                                                                                                                                                                 "
  + "                                                                                                                                                                                                                                                                                      "
  + "   zm_pc_base.xzq,                                                                                                                                                                                                                                                                    "
  + "   zm_pc_base.xzqdm                                                                                                                                                                                                                                                                   "
  + "  FROM (select t1.*,t2.fwzt From zm_pc_yh t0  join zm_pc_base t1 on t0.fwbh=t1.fwbh LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh = t2.fwbh where date(t0.create_on) between '" + startTime + "' and '" + endTime + "')zm_pc_base                                                                  "
  + " WHERE ((zm_pc_base.village)::text IN ( SELECT zm_buildings.villagedm                                                                                                                                                                                                                 "
  + "          FROM zm_buildings))  and   date(create_on) between '" + startTime + "' and '" + endTime + "' and fwzt is null and cs>=3                                                                                                                                                          "
  + " GROUP BY zm_pc_base.xzqdm, zm_pc_base.xzq                                                                                                                                                                                                                                            "
    + "			)nn on mm.xzqdm=nn.xzqdm                                                                                                                                                                                                                                                   "
    + "			                                                                                                                                                                                                                                                                           "
    + "			left join                                                                                                                                                                                                                                                                  "
    + "			(                                                                                                                                                                                                                                                                          "
    + "				SELECT                                                                                                                                                                                                                                                                 "
  + "   count(1) AS two,                                                                                                                                                                                                                                                                   "
  + "                                                                                                                                                                                                                                                                                      "
  + "   zm_pc_base.xzq,                                                                                                                                                                                                                                                                    "
  + "   zm_pc_base.xzqdm                                                                                                                                                                                                                                                                   "
  + "  FROM (select t1.*,t2.fwzt From zm_pc_yh t0  join zm_pc_base t1 on t0.fwbh=t1.fwbh LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh = t2.fwbh where date(t0.create_on) between '" + startTime + "' and '" + endTime + "')zm_pc_base                                                                  "
  + " WHERE ((zm_pc_base.village)::text IN ( SELECT zm_buildings.villagedm                                                                                                                                                                                                                 "
  + "          FROM zm_buildings))  and   date(create_on) between '" + startTime + "' and '" + endTime + "' and fwzt is null and cs=2                                                                                                                                                           "
  + " GROUP BY zm_pc_base.xzqdm, zm_pc_base.xzq                                                                                                                                                                                                                                            "
    + "			)kk on mm.xzqdm=kk.xzqdm                                                                                                                                                                                                                                                   "
    + "			left join                                                                                                                                                                                                                                                                  "
    + "			(                                                                                                                                                                                                                                                                          "
    + "				SELECT                                                                                                                                                                                                                                                                 "
  + "   count(1) AS one,                                                                                                                                                                                                                                                                   "
  + "                                                                                                                                                                                                                                                                                      "
  + "   zm_pc_base.xzq,                                                                                                                                                                                                                                                                    "
  + "   zm_pc_base.xzqdm                                                                                                                                                                                                                                                                   "
  + "  FROM (select t1.*,t2.fwzt From zm_pc_yh t0  join zm_pc_base t1 on t0.fwbh=t1.fwbh LEFT JOIN zm_pc_base_cj t2 ON t1.fwbh = t2.fwbh where date(t0.create_on) between '" + startTime + "' and '" + endTime + "')zm_pc_base                                                                  "
  + " WHERE ((zm_pc_base.village)::text IN ( SELECT zm_buildings.villagedm                                                                                                                                                                                                                 "
  + "          FROM zm_buildings))  and   date(create_on) between '" + startTime + "' and '" + endTime + "' and fwzt is null and cs=1                                                                                                                                                           "
  + " GROUP BY zm_pc_base.xzqdm, zm_pc_base.xzq                                                                                                                                                                                                                                            "
    + "			)hh on mm.xzqdm=hh.xzqdm                                                                                                                                                                                                                                                   "
    + "			) t  where " + strWhere;


            if (sql_count.Contains("insert into", StringComparison.OrdinalIgnoreCase)
          || sql_count.Contains("delete from", StringComparison.OrdinalIgnoreCase)
          || sql_count.Contains("update ", StringComparison.OrdinalIgnoreCase))
            {
                return new RespRet<Page<object>>(false, "不符合规范，禁止执行");
            }
            //执行语句
            var list = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
            int count = _dbContext.Database.GetDbConnection().QueryFirstOrDefault<int>(sql_count);

            return new RespRet<Page<object>>(true, "ok", new Page<object> { Items = list, NextPageIndex = pageIndex, PageTotal = count, Total = count });

        }


        /// <summary>
        /// 导出隐患房屋
        /// </summary>
        /// <param name="pageRequest">type check</param>
        /// <returns></returns>
        [HttpGet("houses/export/zcyhHourseExport")]
        [Produces("application/octet-stream")]
        //[ProducesResponseType(200)]
        //[ProducesResponseType(401)]
        public IActionResult zcyhHourseExport([FromQuery] string xzqdm, [FromQuery] string towndm, [FromQuery] string villagedm, [FromQuery] string type, [FromQuery] string check_id)
        {
            try
            {

                var userNumber = this.CurrentUser();
                string strWhere = "";
                var user = _userRepository.FirstOrDefault(u => u.Number == userNumber && u.IsDelete != 1);
                string district = user.District;
                var options = new JsonSerializerOptions();
                options.PropertyNameCaseInsensitive = true; // 设置属性不区分大小写
                Xzq model = System.Text.Json.JsonSerializer.Deserialize<Xzq>(district, options);


                if (model != null)
                {
                    if (user.RoleType == 1 || user.RoleType == 2)
                    {
                        strWhere += "and  xzq!='东城区' and xzq!='西城区'";
                    }
                    else if (user.RoleType == 3) //区级
                    {
                        strWhere += " and xzqdm='" + model.RegionCode + "'";
                    }
                    else  //乡镇
                    {
                        strWhere += "and xzqdm='" + model.RegionCode.Substring(0, 6) + "' ";
                    }
                }

                if (!string.IsNullOrEmpty(xzqdm))
                {
                    strWhere += " and xzqdm='" + xzqdm + "'";
                }
                if (!string.IsNullOrEmpty(towndm))
                {
                    strWhere += " and towndm='" + towndm + "'";
                }
                if (!string.IsNullOrEmpty(villagedm))
                {
                    strWhere += " and villagedm='" + villagedm + "'";
                }
                string sql = "";
                if (type == "sy")
                {
                    sql = "select ROW_NUMBER() OVER ()index ,* from (select m1.*,hzxm,address,'未整改' zgzt from (select * from (select t1.*,fwzt from zm_pc_yhlog_dx t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh) b1 where fwzt is null) m1 left join zm_pc_base t3 on m1.fwbh=t3.fwbh where zg_value='0' and m1.fwbh not in(select fwbh from zm_pc_base_sqjz where yhly='4' and fwzt is not null)  order by m1.towndm, m1.villagedm) t where 1=1  and check_id='"+check_id+"' " +
                       " " + strWhere;
                }
                else if (type == "zg")
                {
                    sql = "select ROW_NUMBER() OVER ()index ,* from (select m1.*,hzxm,address,'已整改' zgzt from (select * from (select t1.*,fwzt from zm_pc_yhlog_dx t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh) b1 where fwzt is null) m1 left join zm_pc_base t3 on m1.fwbh=t3.fwbh where zg_value='1' and m1.fwbh not in(select fwbh from zm_pc_base_sqjz where yhly='4' and fwzt is not null)  order by m1.towndm, m1.villagedm) t where 1=1  and check_id='"+check_id+"' " +
                       " " + strWhere;
                }
                else if (type == "yh")
                {
                    sql = "select ROW_NUMBER() OVER ()index ,* from (select m1.*,hzxm,address,case when zg_value=1 then '已整改' else '未整改' end zgzt  from (select * from (select t1.*,fwzt from zm_pc_yhlog_dx t1 left join zm_pc_base_cj t2 on t1.fwbh=t2.fwbh) b1 where fwzt is null) m1 left join zm_pc_base t3 on m1.fwbh=t3.fwbh where 1=1  and m1.fwbh not in(select fwbh from zm_pc_base_sqjz where yhly='4' and fwzt is not null)  order by m1.towndm, m1.villagedm) t where 1=1  and check_id='"+check_id+"' " +
                       " " + strWhere;
                }
                //写入缓存
                var data = _dbContext.Database.GetDbConnection().Query<object>(sql).ToList();
                IWorkbook wb = new XSSFWorkbook();
                ISheet sheet = wb.CreateSheet("房屋台账");
                // 第一行 标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("序号");
                row.CreateCell(1).SetCellValue("区名称");
                row.CreateCell(2).SetCellValue("街/乡镇");
                row.CreateCell(3).SetCellValue("村/社区");
                row.CreateCell(4).SetCellValue("房主姓名");
                row.CreateCell(5).SetCellValue("地址");
                row.CreateCell(6).SetCellValue("房屋编号");
                row.CreateCell(7).SetCellValue("隐患状态");
                // 第二行 写数据
                int i = 1;
                foreach (object obj in data)
                {
                    Dictionary<string, object> dictionary = new Dictionary<string, object>();
                    if (obj != null)
                    {

                        var properties = obj as IDictionary<string, object>;
                        row = sheet.CreateRow(i);
                        foreach (var kvp in properties)
                        {
                            //var val = kvp.Value.ToString();
                            if (kvp.Key == "index")
                            {
                                row.CreateCell(0).SetCellValue(i);
                            }
                            else if (kvp.Key == "xzq")
                            {
                                row.CreateCell(1).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "towndm")
                            {
                                row.CreateCell(2).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "villagedm")
                            {
                                row.CreateCell(3).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "hzxm")
                            {
                                row.CreateCell(4).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "address")
                            {
                                row.CreateCell(5).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "fwbh")
                            {
                                row.CreateCell(6).SetCellValue(kvp.Value + "");
                            }
                            else if (kvp.Key == "zgzt")
                            {
                                row.CreateCell(7).SetCellValue(kvp.Value + "");
                            }

                        }

                        i++;
                    }
                }

                // 写 WorkBook信息到 内存流中
                byte[] buffer = null;
                using (MemoryStream ms = new MemoryStream())
                {
                    wb.Write(ms);
                    buffer = ms.ToArray();
                }
                return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "隐患房屋" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx");
            }
            catch (Exception ex)
            {
                throw;
            }
        }

    }

}


    




